Create a MySQL table 'employees' with specific columns, a primary key, and a foreign key using a combination of two or more columns

MySQL Create Tables: Exercise-15 with Solution

15. 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 columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.

Assume the structure of departments table below.

+-----------------+--------------+------+-----+---------+-------+
| 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) | NO   | PRI | 0       |       |
| LOCATION_ID     | decimal(4,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 DEPARTMENT_ID and MANAGER_ID columns, referencing the departments table
    FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID) REFERENCES departments(DEPARTMENT_ID,MANAGER_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.03 sec)

Explanation:

The above MySQL code does:

• 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 the 'EMPLOYEE_ID'.
• Establish a foreign key constraint on the 'DEPARTMENT_ID' and 'MANAGER_ID' columns, referencing the corresponding columns in the "departments" table.
• 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 job_history including columns employee_id, start_date, end_date, job_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 job_id contain only those values which are exists in the jobs table.
Next: 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 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.