MySQL insert into Statement Exercises: Try to insert rows into a table in which a set of columns contains a unique value and that does not exists into the referencing table

MySQL insert into Statement: Exercise-13 with Solution

13. Write a SQL statement to insert rows into the table employees in which a set of columns department_id and manager_id contains a unique value and that combined values must have exists into the table departments.

Sample table departments.


CREATE TABLE IF NOT EXISTS departments ( 
DEPARTMENT_ID integer NOT NULL UNIQUE, 
DEPARTMENT_NAME varchar(30) NOT NULL, 
MANAGER_ID integer DEFAULT NULL, 
LOCATION_ID integer DEFAULT NULL, 
PRIMARY KEY (DEPARTMENT_ID,MANAGER_ID) 
)ENGINE=InnoDB;


INSERT INTO departments VALUES(60,'SALES',201,89);
INSERT INTO departments VALUES(61,'ACCOUNTS',201,89);
INSERT INTO departments VALUES(80,'FINANCE',211,90);

mysql> SELECT * FROM departments;
+---------------+-----------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID |
+---------------+-----------------+------------+-------------+
|            60 | SALES           |        201 |          89 |
|            61 | ACCOUNTS        |        201 |          89 |
|            80 | FINANCE         |        211 |          90 |
+---------------+-----------------+------------+-------------+
3 rows in set (0.00 sec)

Sample table employees.

CREATE TABLE IF NOT EXISTS employees ( 
EMPLOYEE_ID integer NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
JOB_ID varchar(10) NOT NULL, 
SALARY decimal(8,2) DEFAULT NULL, 
MANAGER_ID integer DEFAULT NULL, 
DEPARTMENT_ID integer DEFAULT NULL, 
FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID) 
REFERENCES  departments(DEPARTMENT_ID,MANAGER_ID)
)ENGINE=InnoDB;

Now insert the rows in the employees.

Sample Solution:

INSERT INTO employees VALUES(510,'Alex','Hanes','CLERK',18000,201,60);
INSERT INTO employees VALUES(511,'Kim','Leon','CLERK',18000,211,80);

Let execute the above code in MySQL 5.6 command prompt.

Here is the structure of the table:

mysql> SELECT * FROM employees;
+-------------+------------+-----------+--------+----------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY   | MANAGER_ID | DEPARTMENT_ID |
+-------------+------------+-----------+--------+----------+------------+---------------+
|         510 | Alex       | Hanes     | CLERK  | 18000.00 |        201 |            60 |
|         511 | Kim        | Leon      | CLERK  | 18000.00 |        211 |            80 |
+-------------+------------+-----------+--------+----------+------------+---------------+
2 rows in set (0.00 sec)

The value against department_id and manager_id combination (60,201) and (80,211) are unique in the departmentis(parent) table so, there is no problem arise to insert the rows in the child table employees.

Now insert another row in the employees table.

INSERT INTO employees VALUES(512,'Kim','Leon','CLERK',18000,80,211);

Let execute the above code in MySQL 5.6 command prompt.

mysql> INSERT INTO employees VALUES(512,'Kim','Leon','CLERK',18000,80,211);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hrr`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`D
EPARTMENT_ID`, `MANAGER_ID`) REFERENCES `departments` (`DEPARTMENT_ID`, `MANAGER_ID`))

Here in the above, the value against department_id and manager_id combination (211,80) does not matching with the same combination in departments(parent table) table and that is why the child table employees can not contain the combination of values including department_id and manager_id as specified. Here the primary key - foreign key relationship is being violated and shows the above message.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a SQL statement to insert rows in the job_history table in which one column job_id is containing those values which are exists in job_id column of jobs table.
Next: Write a SQL statement to insert rows into the table employees in which a set of columns department_id and manager_id contains a unique value and that combined values must have exists into the table departments.