How do I solve this primary_key and foreign_key concept?

Question

I ran:

create table Workers_Information
(
    WORKER_ID int not null auto_increment primary key, 
    FIRST_NAME varchar(55),
    LAST_NAME_NAME varchar(55),
    SALARY int,
    JOINING_DATE date,
    DEPARTMENT varchar(75)
);

insert into workers_information(WORKER_ID, FIRST_NAME, LAST_NAME_NAME, SALARY, JOINING_DATE, DEPARTMENT)
values (1, "Monika", "Arora", 100000, "14-02-20", "HR"),
       (2, "Niharika","Verma",80000,"14-06-11","Admin"),
       (3, "Vishal","Singhal",300000,"14-02-20","HR"),
       (4, "Amitabh","Singh",500000,"14-02-20","Admin"),
       (5, "Vivek","Bhati",500000,"14-06-11","Admin"),
       (6, "Vipul","Diwan",200000,"14-06-11","Account"),
       (7, "Satish","Kumar",75000,"14-01-20","Account"),
       (8, "Geetika","Chauhan",90000,"14-04-11","Admin"),
       (9, "Pradnya","Kamble",10500,"14-02-20","Information Technology"),
       (10, "Mahesh ","Kamble",22500,"14-06-11","Operations");
  
create table Bonus_Table 
(
    Bonus_Code int not null auto_increment primary key,
    BONUS_DATE date,
    BONUS_AMOUNT int,
    WORKER_REF_ID int
);

insert into bonus_table(Bouns_Code, BONUS_DATE, BONUS_AMOUNT, WORKER_REF_ID)
values (101, "16-02-20", 5000, 1),
       (102, "16-06-11", 3000, 2),
       (103, "16-02-20", 4000, 3),
       (104, "16-02-20", 4500, 4),
       (105, "16-06-11", 3500, 5),
       (106, "16-10-01", 3500, 6),
       (107, "17-01-21", 3250, 7),
       (108, "17-05-13", 3000, 8),
       (109, "17-09-02", 2750, 9),
       (110, "17-12-23", 2500, 10),
       (111, "18-04-14", 2250, 11),
       (112, "18-08-04", 2000, 12),
       (113, "18-11-24", 1750, 13),
       (114, "19-03-16", 1500, 14),
       (115, "19-07-06", 1250, 15);
  
alter table bonus_table 
    add foreign key(WORKER_REF_ID)  
        references workers_information(WORKER_ID)
            On Update Cascade 
            On Delete cascade

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`imashgamer`.`#sql-2284_e`, CONSTRAINT `bonus_table_ibfk_1` FOREIGN KEY (`WORKER_REF_ID`) REFERENCES `workers_information` (`WORKER_ID`) ON DELETE CASCADE ON UPDATE CASCADE)}

What RDBMS are you using / for which database is this code written? — marc_s, Apr 25 '23 at 18:17
Does this answer your question? [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](https://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) — philipxy, Apr 25 '23 at 21:53
You add the foreign key in order to have it guaranteed that you cannot insert a row into bonus_table with a worker_ref_id that does not exist in the workers_information table. As there are already rows in the bonus_table that don't have a match in workers_information, the DBMS complains when you try to add the foreign key. So, everything works as you want it to work. Either add workers_information rows for the worker IDs 11 to 15 to workers_information or remove the rows with these worker IDs from the bonus_table and the DBMS will be fine with your constraint. — Thorsten Kettner, Apr 25 '23 at 23:36
And yes, I too wonder which DBMS you are using. Double quotes denote names in SQL, but you are using them for string literals (e.g. `"Monika"` should be `'Monika'` in SQL). And `"14-02-20"` is not a valid date literal. `DATE '2020-02-14'` is. — Thorsten Kettner, Apr 25 '23 at 23:42

score -1 · Answer 1 · answered Apr 25 '23 at 21:58

-1

You have worker_ref_id values (11-15) that don’t exist in the workers_information table

answered Apr 25 '23 at 21:58

NickW

8,430
2
6
19

How do I solve this primary_key and foreign_key concept?

1 Answers1