Create a MySQL table 'employees' with a unique 'employee_id' column and foreign keys referencing another table. Use the InnoDB Engine for table creation

Last update on January 02 2024 12:47:30 (UTC/GMT +8 hours)

MySQL Create Tables: Exercise-16 with Solution

16. Write a MySQL query to create a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column department_id, reference by the column department_id of departments table, can contain only those values which are exists in the departments table and another 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.

"A foreign key constraint is not required merely to join two tables. For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table." - Reference dev.mysql.com

Assume that the structure of two tables departments and jobs.

+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| DEPARTMENT_ID   | decimal(4,0) | NO   | PRI | 0       |       |
| DEPARTMENT_NAME | varchar(30)  | NO   |     | NULL    |       |
| MANAGER_ID      | decimal(6,0) | YES  |     | NULL    |       |
| LOCATION_ID     | decimal(4,0) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+


+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID     | varchar(10)  | NO   | PRI |         |       |
| JOB_TITLE  | varchar(35)  | NO   |     | NULL    |       |
| MIN_SALARY | decimal(6,0) | YES  |     | NULL    |       |
| 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 email addresses of employees with a maximum length of 25 characters, marked as NOT NULL
    EMAIL varchar(25) NOT NULL,

    -- Column to store phone numbers of employees with a maximum length of 20 characters, marked as DEFAULT NULL
    PHONE_NUMBER varchar(20) DEFAULT NULL,

    -- Column to store the hire date of employees, marked as NOT NULL
    HIRE_DATE date NOT NULL,

    -- Column to store job IDs of employees with a maximum length of 10 characters, marked as NOT NULL
    JOB_ID varchar(10) NOT NULL,

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

    -- Column to store commission percentages of employees with decimal precision of 2, 2, marked as DEFAULT NULL
    COMMISSION_PCT decimal(2,2) DEFAULT NULL,

    -- Column to store manager IDs of employees with decimal precision of 6, 0, marked as DEFAULT NULL
    MANAGER_ID decimal(6,0) DEFAULT NULL,

    -- Column to store department IDs of employees with decimal precision of 4, 0, marked as DEFAULT NULL
    DEPARTMENT_ID decimal(4,0) DEFAULT NULL,

    -- Creating a FOREIGN KEY constraint on the DEPARTMENT_ID column, referencing the departments table
    FOREIGN KEY(DEPARTMENT_ID) REFERENCES departments(DEPARTMENT_ID),

    -- Creating a FOREIGN KEY constraint on the JOB_ID column, referencing the jobs table
    FOREIGN KEY(JOB_ID) REFERENCES jobs(JOB_ID)
)
-- 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    |       |
| 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.01 sec)

Explanation:

The above MySQL:

Create a table named "employees" if it doesn't already exist to store employee information.
Define columns for employee details such as ID, name, email, phone number, hire date, job, salary, commission percentage, manager ID, and department ID.
Sets constraints on certain columns like NOT NULL, DEFAULT NULL, and defines a primary key on 'EMPLOYEE_ID'.
Establish foreign key constraints on the 'DEPARTMENT_ID' and 'JOB_ID' columns, referencing the corresponding columns in the 'departments' and 'jobs' tables.
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 a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.
Next: 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 UPDATE CASCADE action allows you to perform cross-table update and ON DELETE RESTRICT action reject the deletion. The default action is ON DELETE RESTRICT.