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:

-- This SQL statement creates a new table named 'job_history' defining the structure and constraints for the table columns.

CREATE TABLE job_history ( 
    EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY, -- Defines a column 'EMPLOYEE_ID' to store employee IDs as decimal numbers with a precision of 6 digits and no decimal places. The column cannot contain NULL values and serves as the primary key for the table.
    START_DATE date NOT NULL, -- Defines a column 'START_DATE' to store the start dates of job history records. The column cannot contain NULL values.
    END_DATE date NOT NULL, -- Defines a column 'END_DATE' to store the end dates of job history records. The column cannot contain NULL values.
    JOB_ID varchar(10) NOT NULL, -- Defines a column 'JOB_ID' to store job IDs as strings with a maximum length of 10 characters. The column cannot contain NULL values.
    DEPARTMENT_ID decimal(4,0) DEFAULT NULL, -- Defines a column 'DEPARTMENT_ID' to store department IDs as decimal numbers with a precision of 4 digits and no decimal places. If no value is provided during insertion, it defaults to NULL.
    FOREIGN KEY (job_id) REFERENCES jobs(job_id) -- Defines a foreign key constraint on the 'JOB_ID' column, referencing the 'JOB_ID' column in the 'jobs' table.
);

Explanation:

• The CREATE TABLE statement creates a new table named 'job_history' with the specified columns and constraints.
• Each column definition specifies the column name, data type, and optional constraints.
• decimal(6,0) indicates a decimal number with a precision of 6 digits and no decimal places for the 'EMPLOYEE_ID' column.
• The NOT NULL constraint ensures that 'EMPLOYEE_ID', 'START_DATE', 'END_DATE', and 'JOB_ID' columns cannot contain NULL values.
• The PRIMARY KEY constraint sets the 'EMPLOYEE_ID' column as the primary key for the table, ensuring uniqueness and providing a unique identifier for each row.
• date data type is used for both 'START_DATE' and 'END_DATE' columns to store date values.
• varchar(10) indicates a variable-length character string with a maximum length of 10 characters for the 'JOB_ID' column.
• The DEFAULT NULL constraint specifies that if no value is provided during insertion, the 'DEPARTMENT_ID' column defaults to NULL.
• The FOREIGN KEY constraint establishes a relationship between the 'JOB_ID' column in the 'job_history' table and the 'JOB_ID' column in the 'jobs' table. This ensures referential integrity, meaning that every value in the 'JOB_ID' column of the 'job_history' table must exist in the 'JOB_ID' column of the 'jobs' table.

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.