Should I be entering my table data before or after I create all the tables and how I want them linked together using foreign keys?

Question

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.

Answer 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?