MySQL Alter Table Statement Exercises: 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

MySQL Alter Table Statement: Exercise-13 with Solution

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

Here is the structure of the table job_history.

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    |       |
+---------------+---------+------+-----+---------+-------+

Code:

 -- This SQL statement is used to alter the 'job_history' table by dropping a named foreign key constraint.
-- The named foreign key constraint 'fk_job_id' is being removed.

ALTER TABLE job_history

-- Drop the named foreign key constraint 'fk_job_id' from the 'job_history' table.
DROP FOREIGN KEY fk_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 index file.

+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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         |           1 |     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 change the structure of the 'job_history' table.
• DROP FOREIGN KEY fk_job_id;: It drops (removes) the named foreign key constraint 'fk_job_id' from the 'job_history' table. The 'fk_job_id' is the name assigned to the foreign key constraint when it was created.

Here in the above the foreign key index file is appearing although the foreign key have been dropped, but the action of foreign key constraint have lost.

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

Previous: 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.
Next: Write a SQL statement to add an index named indx_job_id on job_id column in the table job_history.