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;


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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.