w3resource

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.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql-exercises/alter-table-statement/alter-table-exercise-12.php