w3resource

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

MySQL Alter Table Statement: Exercise-11 with Solution

Write a MySQL statement to add a foreign key 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 foreign key constraint.
-- The foreign key is added on the 'job_id' column, referencing the 'job_id' column in the 'jobs' table.
	
ALTER TABLE job_history

-- Add a foreign key constraint to the 'job_id' column in the 'job_history' table.
ADD FOREIGN KEY(job_id)

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

Let execute the above code in MySQL command prompt

Now see the structure of the table job_history 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 | 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 FOREIGN KEY(job_id): It adds a foreign key constraint to the 'job_id' column in the 'job_history' table.
  • REFERENCES jobs(job_id);: This 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. This ensures that values in the 'job_id' column of 'job_history' must exist in the 'job_id' column of the 'jobs' table. This establishes a relationship between the two tables.

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

Previous: Write a SQL statement to drop the existing primary from the table locations on a combination of columns location_id and country_id.
Next: Write a SQL 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.

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://w3resource.com/mysql-exercises/alter-table-statement/alter-table-exercise-11.php