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

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:

ALTER TABLE job_history
DROP FOREIGN KEY fk_job_id;

Let execute the above code in MySQL 5.6 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      |         |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

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.