MySQL Alter Table Statement Exercises: Add a foreign key constraint named fk_job_id on job_id column of job_history table referencing to the primary key job_id of jobs table

MySQL Alter Table Statement: Exercise-12 with Solution

Write a MySQL statement to add a foreign key constraint named fk_job_id on job_id column of job_history table referencing to the primary key job_id of jobs table.

Here is the structure of the table jobs and job_history.

mysql> SHOW COLUMNS FORM jobs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID     | int(11)      | NO   | PRI | NULL    |       |
| JOB_TITLE  | varchar(35)  | NO   |     | NULL    |       |
| MIN_SALARY | decimal(6,0) | YES  |     | NULL    |       |
| MAX_SALARY | decimal(6,0) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID   | int(11) | NO   |     | NULL    |       |
| START_DATE    | date    | YES  |     | NULL    |       |
| HIRE_DATE     | date    | YES  |     | NULL    |       |
| JOB_ID        | int(11) | NO   |     | NULL    |       |
| DEPARTMENT_ID | int(11) | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

Code:

 -- This SQL statement is used to alter the 'job_history' table by adding a named foreign key constraint.
-- The foreign key is added on the 'job_id' column, referencing the 'job_id' column in the 'jobs' table.
-- Additional options are specified for the foreign key constraint, including ON UPDATE RESTRICT and ON DELETE CASCADE.

ALTER TABLE job_history 

-- Add a named foreign key constraint 'fk_job_id' to the 'job_id' column in the 'job_history' table.
ADD CONSTRAINT fk_job_id 

-- Specify that the foreign key is on the 'job_id' column.
FOREIGN KEY (job_id) 

-- Specify the referenced table and column for the foreign key constraint.
REFERENCES jobs(job_id) 

-- Specify the action to be taken when the referenced column is updated (RESTRICT).
ON UPDATE RESTRICT 

-- Specify the action to be taken when the referenced row is deleted (CASCADE).
ON DELETE CASCADE;

Let execute the above code in MySQL command prompt

Now see the structure of the table locations after being altered.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID   | int(11) | NO   |     | NULL    |       |
| START_DATE    | date    | YES  |     | NULL    |       |
| HIRE_DATE     | date    | YES  |     | NULL    |       |
| JOB_ID        | int(11) | NO   | MUL | NULL    |       |
| DEPARTMENT_ID | int(11) | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

Now see the created index file.

mysql> SHOW INDEX FROM job_history;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| job_history |          1 | fk_job_id |            1 | JOB_ID      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Explanation:

Here's a brief explanation of the above MySQL code:

• ALTER TABLE job_history: This part of the statement indicates that you want to make changes to the structure of the 'job_history' table.
• ADD CONSTRAINT fk_job_id: This specifies the action to be taken. It adds a named foreign key constraint 'fk_job_id' to the 'job_id' column in the 'job_history' table.
• FOREIGN KEY (job_id): This part of the statement specifies that the foreign key is on the 'job_id' column.
• REFERENCES jobs(job_id): This part of the statement specifies the referenced table and column for the foreign key constraint. It indicates that the 'job_id' column in 'job_history' should refer to the 'job_id' column in the 'jobs' table.
• ON UPDATE RESTRICT: In this case, the update is restricted, meaning that the update is not allowed if it breaks the foreign key relationship.
• ON DELETE CASCADE: In this case, a cascade delete is performed, meaning that if a row in 'jobs' is deleted, the corresponding rows in 'job_history' with matching 'job_id' values will also be deleted.

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

Previous: Write a SQL statement to add a foreign key on job_id column of job_history table referencing to the primary key job_id of jobs table.
Next: Write a SQL statement to drop the existing foreign key fk_job_id from job_history table on job_id column which is referencing to the job_id of jobs table.