0

enter image description here

I am attempting to create and link four tables. I have attached the screenshot for the tables. I begin my process as such:

CREATE TABLE employee 
(
    emp_id VARCHAR(40) PRIMARY KEY,
    emp_name VARCHAR(40),
    salary VARCHAR(40),
    dept_id VARCHAR(40),
    man_id VARCHAR(40)
);

CREATE TABLE manager 
(
    man_id VARCHAR(40) PRIMARY KEY,
    man_name VARCHAR(40),
    dept_id VARCHAR(40)
);

Once I create the tables, I then add in the values:

INSERT INTO employee VALUES ('E1', 'Rahul', 15000, 'D1', 'M1');
INSERT INTO employee VALUES ('E2', 'Manoj', 15000, 'D1', 'M1');
INSERT INTO employee VALUES ('E3', 'James', 55000, 'D2', 'M2');
INSERT INTO employee VALUES ('E4', 'Michael', 25000, 'D2', 'M2');
INSERT INTO employee VALUES ('E5', 'Ali', 20000, 'D10', 'M3');
INSERT INTO employee VALUES ('E6', 'Robin', 35000, 'D10', 'M3');

INSERT INTO manager VALUES ('M1', 'Prem', 'D3');
INSERT INTO manager VALUES ('M2', 'Shripadh', 'D4');
INSERT INTO manager VALUES ('M3', 'Nick', 'D1');
INSERT INTO manager VALUES ('M4', 'Cory', 'D1');

From here, I alter my employee table so that the man_id on my employee table references the manager man_id:

ALTER TABLE employee 
    ADD FOREIGN KEY (man_id) REFERENCES manager(man_id)
        ON DELETE SET NULL;

From here, I then attempt to alter the employee table one more time as to add a foreign key on the dept_id that references the department dept_id.

ALTER TABLE employee
    ADD FOREIGN KEY (dept_id) REFERENCES department (dept_id)
        ON DELETE SET NULL;

However, once I enter this command, I get the following error below:

Error:

Cannot add or update a child row: a foreign key constraint fails (colin2.#sql-2434_39, CONSTRAINT employee_ibfk_2 FOREIGN KEY (dept_id) REFERENCES department (dept_id) ON DELETE SET NULL) Error Code: ER_NO_REFERENCED_ROW_2

I am unsure as to what is causing this. However, the strange thing is that if I do not populate my tables first, then this command will go through just fine. My question is why am I running into this issue, and if someone could mock up the step by step process of how they would create these tables and how they would map out the foreign keys, I would greatly appreciate it!!

I attempted to alter the employee table to make the dept_id a foreign key referencing the department dept_id but I had no such luck. Please see above.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sullivan11342
  • 1
  • 2
  • **(1)** Create all your tables, including all your foreign key references. **(2)** Insert all the lookup data, like departments etc., that will be referenced by the actual data. **(3)** Once everything is ready, add your actual data to the tables - all the references and the referenced data should be in place now, so this should work smoothly – marc_s Jul 01 '23 at 16:36
  • @Marc_s Hi Marc, thank you for your reply here I appreciate your time. I have gone ahead and created the tables, and then afterwards I altered the tables by adding the foreign keys, and that all went well and I received no errors. However, I am unsure what you mean by "insert all the lookup data like depts etc" and how that would be different from the actual data? When I attempt to add in the actual data, I get the message: Cannot add or update a child row: a foreign key constraint fails (`colin2`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`man_id`) REFERENCES `manager` (`man_id`)) – sullivan11342 Jul 01 '23 at 17:42
  • You need to insert the data that is being referred to before you insert the data that is doing the referring. So if you want to insert an employee record that refers to a manager record, that manager record must already exist. It’s logical really if you think about it, how can refer to a record that doesn’t exist? – NickW Jul 01 '23 at 20:14

1 Answers1

1

When you're inserting the employee data you're including department IDs. Then you try to add the foreign key constraint without first having inserted the actual department data so immediately it fails because there are department IDs in the employee table that are not in the department table.

Create all your tables first, including your foreign key constraints, before inserting any data at all.

Insert all your reference data, starting with departments, then managers, projects and finally employees.

I am surprised that your managers are in a separate table. Are they not also employees?

Tangentially Perpendicular
  • 5,012
  • 4
  • 11
  • 29
  • Thank you so much!! Okay so that definitely made a difference. I first created the employee table, then I created the remaining tables. I then inserted the data into all of my tables EXCEPT for employee. From there I was able to alter the employee table and add foreign keys. However, past that Im only able to add the first 4 rows of employee data. I can't add it for Robin or Ali, I get message: Error: Cannot add or update a child row: a foreign key constraint fails (`colin2`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`) ON DELETE SET NULL – sullivan11342 Jul 01 '23 at 18:22
  • also, this is a table I found online not an actual one from my own work lol apologies. – sullivan11342 Jul 01 '23 at 19:21
  • If you're successfully adding some data then your data structure is good. If some data is failing the FK then something is wrong with your data. Look at the FKs applied to the Employee table. Do you have all the required entries in your Department table? – Tangentially Perpendicular Jul 01 '23 at 21:02