Create a MySQL table named 'job_history' ensuring that the 'employee_id' column has no duplicate values during insertion and 'job_id' contains only existing values from the 'jobs' table

MySQL Create Tables: Exercise-14 with Solution

14. Write a MySQL query 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.

Here is the structure of the table jobs;

+------------+--------------+------+-----+---------+-------+
| 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 'job_history' to store historical job information for employees

CREATE TABLE job_history(
    -- 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 the start date of the job, marked as NOT NULL
    START_DATE date NOT NULL,

    -- Column to store the end date of the job, marked as NOT NULL
    END_DATE date NOT NULL,

    -- Column to store job IDs, marked as NOT NULL
    JOB_ID varchar(10) NOT NULL,

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

    -- Creating a FOREIGN KEY constraint on the JOB_ID column, referencing the jobs table
    FOREIGN KEY (job_id) REFERENCES jobs(job_id)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC job_history;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID   | decimal(6,0) | NO   | PRI | NULL    |       |
| START_DATE    | date         | NO   |     | NULL    |       |
| END_DATE      | date         | NO   |     | NULL    |       |
| JOB_ID        | varchar(10)  | NO   | MUL | NULL    |       |
| DEPARTMENT_ID | decimal(4,0) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

Explanation:

The above MySQL code does the following:

• Create a table named "job_history" to store historical job information for employees.
• Define columns for:
• EMPLOYEE_ID: Employee IDs with decimal precision of 6, 0, marked as NOT NULL and serving as the PRIMARY KEY.
• START_DATE: Start date of the job, marked as NOT NULL.
• END_DATE: End date of the job, marked as NOT NULL.
• JOB_ID: Job IDs, marked as NOT NULL.
• DEPARTMENT_ID: Department IDs with decimal precision of 4, 0, marked as DEFAULT NULL.
• Create a FOREIGN KEY constraint on the JOB_ID column, referencing the jobs table.

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

Previous: Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.
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 columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.