MySQL Create Table Exercises: Create a table employees, and make sure that, the employee_id column should be unique, and set foreign keys referencing to another table using ON DELETE CASCADE and ON UPDATE RESTRICT actions
MySQL Create Tables: Exercise-18 with Solution
18. Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE CASCADE that lets you allow to delete records in the employees(child) table that refer to a record in the jobs(parent) table when the record in the parent table is deleted and the ON UPDATE RESTRICT actions reject any updates.
Assume that the structure of the table jobs and InnoDB Engine have been used to create the table jobs.
CREATE TABLE IF NOT EXISTS jobs ( JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', MIN_SALARY decimal(6,0) DEFAULT 8000, MAX_SALARY decimal(6,0) DEFAULT NULL )ENGINE=InnoDB; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | JOB_ID | int(11) | NO | PRI | NULL | | | JOB_TITLE | varchar(35) | NO | | | | | MIN_SALARY | decimal(6,0) | YES | | 8000 | | | MAX_SALARY | decimal(6,0) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+
Sample Solution:
CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
FIRST_NAME varchar(20) DEFAULT NULL,
LAST_NAME varchar(25) NOT NULL,
JOB_ID INTEGER NOT NULL,
SALARY decimal(8,2) DEFAULT NULL,
FOREIGN KEY(JOB_ID)
REFERENCES jobs(JOB_ID)
ON DELETE CASCADE ON UPDATE RESTRICT
)ENGINE=InnoDB;
Let execute the above code in MySQL 5.6 command prompt
Here is the structure of the table:
mysql> DESC employees; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | EMPLOYEE_ID | decimal(6,0) | NO | PRI | NULL | | | FIRST_NAME | varchar(20) | YES | | NULL | | | LAST_NAME | varchar(25) | NO | | NULL | | | EMAIL | varchar(25) | NO | | NULL | | | PHONE_NUMBER | varchar(20) | YES | | NULL | | | HIRE_DATE | date | NO | | NULL | | | JOB_ID | varchar(10) | NO | | NULL | | | SALARY | decimal(8,2) | YES | | NULL | | | COMMISSION_PCT | decimal(2,2) | YES | | NULL | | | MANAGER_ID | decimal(6,0) | YES | | NULL | | | DEPARTMENT_ID | decimal(4,0) | YES | MUL | NULL | | +----------------+--------------+------+-----+---------+-------+ 11 rows in set (0.09 sec)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to create a table employees, and make sure that, the employee_id column contain no duplicate values, and set foreign keys referencing to another table using ON UPDATE CASCADE and ON DELETE RESTRICT.
Next: Write a SQL statement to create Table Exercises: Create a table employees, and make sure that, the employee_id column should be unique, and set foreign keys referencing to another table using ON DELETE SET NULL and ON UPDATE SET NULL action
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