Create a MySQL table 'employees' with a unique 'employee_id' and foreign keys referencing another table using ON DELETE SET NULL and ON UPDATE SET NULL actions

MySQL Create Tables: Exercise-19 with Solution

19. Write a MySQL query 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 SET NULL action will set the foreign key column values in the child table(employees) to NULL when the record in the parent table(jobs) is deleted, with a condition that the foreign key column in the child table must accept NULL values and the ON UPDATE SET NULL action resets the values in the rows in the child table(employees) to NULL values when the rows in the parent table(jobs) are updated.

Assume that the structure of two 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:

-- Creating a table named 'employees' if it doesn't already exist to store employee information

CREATE TABLE IF NOT EXISTS employees(
    -- Column to store employee IDs with decimal precision of 6, 0, marked as NOT NULL and serving as the PRIMARY KEY
    EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,

    -- Column to store first names of employees with a maximum length of 20 characters, marked as DEFAULT NULL
    FIRST_NAME varchar(20) DEFAULT NULL,

    -- Column to store last names of employees with a maximum length of 25 characters, marked as NOT NULL
    LAST_NAME varchar(25) NOT NULL,

    -- Column to store job IDs of employees as INTEGER
    JOB_ID INTEGER,

    -- Column to store salaries of employees with decimal precision of 8, 2, marked as DEFAULT NULL
    SALARY decimal(8,2) DEFAULT NULL,

    -- Creating a FOREIGN KEY constraint on the JOB_ID column, referencing the jobs table
    FOREIGN KEY(JOB_ID) REFERENCES jobs(JOB_ID)
    -- Setting actions for ON DELETE (SET NULL) and ON UPDATE (SET NULL)
    ON DELETE SET NULL 
    ON UPDATE SET NULL
)
-- Setting the storage engine to InnoDB for transactional support
ENGINE=InnoDB;

Let execute the above code in MySQL 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    |       |
| JOB_ID         | varchar(10)  | NO   |     | NULL    |       |
| SALARY         | decimal(8,2) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

Explanation:

According to the MySQL code above:

• Create a table named "employees" if it doesn't already exist to store employee information.
• Define columns for employee details such as ID, first name, last name, job ID, and salary.
• Sets constraints on certain columns like NOT NULL, DEFAULT NULL, and defines a primary key on the 'EMPLOYEE_ID'.
• Establish a foreign key constraint on the 'JOB_ID' column, referencing the "jobs" table.
• Specifies that if a referenced record in the "jobs" table is deleted (ON DELETE SET NULL), the corresponding 'JOB_ID' in the "employees" table will be set to NULL. Similarly, if the 'JOB_ID' in the "jobs" table is updated (ON UPDATE SET NULL), the corresponding 'JOB_ID' in the "employees" table will be set to NULL.
• Set the storage engine to InnoDB for transactional support.

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

Previous: 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 CASCADE and ON UPDATE RESTRICT actions reject any updates.
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 NO ACTION and the ON UPDATE NO ACTION actions which will reject the deletion and any updates