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 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.
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:
ALTER TABLE job_history
ADD CONSTRAINT fk_job_id
FOREIGN KEY (job_id)
REFERENCES jobs(job_id)
ON UPDATE RESTRICT
ON DELETE CASCADE;
Let execute the above code in MySQL 5.6 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 | | +-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
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?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework