Insert rows into the MySQL 'job_history' table with values in the 'job_id' column that already exist in the 'jobs' table

MySQL insert into Statement: Exercise-12 with Solution

12. Write a MySQL query 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.

Sample table jobs.


CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT 20000
)ENGINE=InnoDB;

INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1001,'OFFICER');
INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1002,'CLERK');

+--------+-----------+------------+------------+
| JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY |
+--------+-----------+------------+------------+
|   1001 | OFFICER   |       8000 |      20000 |
|   1002 | CLERK     |       8000 |      20000 |
+--------+-----------+------------+------------+
2 rows in set (0.00 sec)

Sample table job_history;

CREATE TABLE job_history ( 
EMPLOYEE_ID integer NOT NULL PRIMARY KEY, 
JOB_ID integer NOT NULL, 
DEPARTMENT_ID integer DEFAULT NULL, 
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
)ENGINE=InnoDB;

Sample Solution:

-- Inserting a new record into the 'job_history' table
INSERT INTO job_history VALUES(501, 1001, 60);

Let execute the above code in MySQL 5.6 command prompt.

Here is the structure of the table:

mysql> SELECT * FROM job_history;
+-------------+--------+---------------+
| EMPLOYEE_ID | JOB_ID | DEPARTMENT_ID |
+-------------+--------+---------------+
|         501 |   1001 |            60 |
+-------------+--------+---------------+
1 row in set (0.00 sec)

The value against job_id is 1001 which is exists in the job_id column of the jobs table, so no problem arise.

Now insert another row in the job_history table.

INSERT INTO job_history VALUES(502,1003,80);

Let execute the above code in MySQL command prompt.

mysql> INSERT INTO job_history VALUES(502,1003,80);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hrr`.`job_history`, CONSTRAINT `job_history_ibfk_1`
 (`JOB_ID`) REFERENCES `jobs` (`JOB_ID`))

Here in the above, the value against job_id is 1003 which is not exists in the job_id column of the jobs(parent table) table and that is why the child table job_history can not contain the value of job_id as specified. Here the primary key - foreign key relationship is violating and shows the above message.

Explanation:

Here's a breakdown of the above MySQL query:

• INSERT INTO job_history: Specifies the insertion operation into the 'job_history' table.
• VALUES(501, 1001, 60): Specifies the values to be inserted into the columns of the 'job_history' table. In this case:
• 501 is inserted into the 'EMPLOYEE_ID' column.
• 1001 is inserted into the 'JOB_ID' column.
• 60 is inserted into the 'DEPARTMENT_ID' column

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

Previous:Write a MySQL query to insert records into the table countries to ensure that the country_id column will not contain any duplicate data and this will be automatically incremented and the column country_name will be filled up by 'N/A' if no value assigned for that column.
Next:Write a MySQL query 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.