PostgreSQL Create Table: Create a table to allow one of the columns to contain a unique value and another one is referencing to the column of another table

13. Write a SQL statement to create a table job_history, including employee_id, start_date, end_date, job_id and department_id and make sure that, the employee_id column does not contain any duplicate values at the time of insertion and the foreign key column job_id contain only those values which exist in the jobs table.

Here is the structure of the table jobs;

Indexes:
    "jobs_pkey" PRIMARY KEY, btree (job_id)

Sample Solution:

Code:

CREATE TABLE job_history ( 
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY, 
START_DATE date NOT NULL, 
END_DATE date NOT NULL, 
JOB_ID varchar(10) NOT NULL, 
DEPARTMENT_ID decimal(4,0) DEFAULT NULL, 
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
);

Output:

postgres=# CREATE TABLE job_history (
postgres(# EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
postgres(# START_DATE date NOT NULL,
postgres(# END_DATE date NOT NULL,
postgres(# JOB_ID varchar(10) NOT NULL,
postgres(# DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
postgres(# FOREIGN KEY (job_id) REFERENCES jobs(job_id)
postgres(# );
CREATE TABLE

Here is the command to see the structure of the created table :

postgres=# \d job_history;
                 Table "public.job_history"
    Column     |         Type          |       Modifiers
---------------+-----------------------+-----------------------
 employee_id   | numeric(6,0)          | not null
 start_date    | date                  | not null
 end_date      | date                  | not null
 job_id        | character varying(10) | not null
 department_id | numeric(4,0)          | default NULL::numeric
Indexes:
    "job_history_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
    "job_history_job_id_fkey" FOREIGN KEY (job_id) REFERENCES jobs(job_id)

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 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 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 did not contain any duplicate values 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 exist in the departments table.