w3resource

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;

   Column   |         Type          |               Modifiers
------------+-----------------------+----------------------------------------
 job_id     | character varying(10) | not null default ''::character varying
 job_title  | character varying(35) | not null
 min_salary | numeric(6,0)          | default NULL::numeric
 max_salary | numeric(6,0)          | default NULL::numeric
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.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming