PostgreSQL Create Table: Create a table to allow one of the columns to contain unique values and another two columns are referenced to the columns of another two tables

Last update on March 16 2024 12:21:22 (UTC/GMT +8 hours)

15. 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 column department_id, reference by the column department_id of departments table, can contain only those values which exist in the departments table and another foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which exist in the jobs table.

Assume that the structure of two tables departments and jobs.

Indexes:
    "departments_pkey" PRIMARY KEY, btree (department_id)

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

Sample Solution:

Code:

-- This SQL statement creates a new table named 'employees' if it does not already exist,
-- defining the structure and constraints for the table columns.

CREATE TABLE IF NOT EXISTS employees ( 
    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.
    FIRST_NAME varchar(20) DEFAULT NULL, -- Defines a column 'FIRST_NAME' to store first names of employees as strings with a maximum length of 20 characters. If no value is provided during insertion, it defaults to NULL.
    LAST_NAME varchar(25) NOT NULL, -- Defines a column 'LAST_NAME' to store last names of employees as strings with a maximum length of 25 characters. The column cannot contain NULL values.
    EMAIL varchar(25) NOT NULL, -- Defines a column 'EMAIL' to store email addresses of employees as strings with a maximum length of 25 characters. The column cannot contain NULL values.
    PHONE_NUMBER varchar(20) DEFAULT NULL, -- Defines a column 'PHONE_NUMBER' to store phone numbers of employees as strings with a maximum length of 20 characters. If no value is provided during insertion, it defaults to NULL.
    HIRE_DATE date NOT NULL, -- Defines a column 'HIRE_DATE' to store the hire dates of employees. 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.
    SALARY decimal(8,2) DEFAULT NULL, -- Defines a column 'SALARY' to store salaries of employees as decimal numbers with a precision of 8 digits and 2 decimal places. If no value is provided during insertion, it defaults to NULL.
    COMMISSION_PCT decimal(2,2) DEFAULT NULL, -- Defines a column 'COMMISSION_PCT' to store commission percentages of employees as decimal numbers with a precision of 2 digits and 2 decimal places. If no value is provided during insertion, it defaults to NULL.
    MANAGER_ID decimal(6,0) DEFAULT NULL, -- Defines a column 'MANAGER_ID' to store manager IDs as decimal numbers with a precision of 6 digits and no decimal places. If no value is provided during insertion, it defaults to NULL.
    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 (DEPARTMENT_ID) REFERENCES departments(DEPARTMENT_ID), -- Defines a foreign key constraint on the 'DEPARTMENT_ID' column, referencing the 'DEPARTMENT_ID' column in the 'departments' table.
    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 IF NOT EXISTS statement creates a new table only if it does not already exist in the database.
The table is named 'employees'.
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', 'LAST_NAME', 'EMAIL', 'HIRE_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.
varchar(20) indicates a variable-length character string with a maximum length of 20 characters for the 'FIRST_NAME' column.
varchar(25) indicates a variable-length character string with a maximum length of 25 characters for the 'LAST_NAME', 'EMAIL', and 'PHONE_NUMBER' columns.
date data type is used for the 'HIRE_DATE' column to store date values.
varchar(10) indicates a variable-length character string with a maximum length of 10 characters for the 'JOB_ID' column.
decimal(8,2) indicates a decimal number with a precision of 8 digits and 2 decimal places for the 'SALARY' column.
decimal(2,2) indicates a decimal number with a precision of 2 digits and 2 decimal places for the 'COMMISSION_PCT' column.
decimal(6,0) indicates a decimal number with a precision of 6 digits and no decimal places for the 'MANAGER_ID' column.
decimal(4,0) indicates a decimal number with a precision of 4 digits and no decimal places for the 'DEPARTMENT_ID' column.
The DEFAULT NULL constraint specifies that if no value is provided during insertion, the 'PHONE_NUMBER', 'SALARY', 'COMMISSION_PCT', 'MANAGER_ID', and 'DEPARTMENT_ID' columns default to NULL.
Two FOREIGN KEY constraints establish relationships between the 'DEPARTMENT_ID' column in the 'employees' table and the 'DEPARTMENT_ID' column in the 'departments' table,

Output:

postgres=# CREATE TABLE IF NOT EXISTS employees (
postgres(# EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
postgres(# FIRST_NAME varchar(20) DEFAULT NULL,
postgres(# LAST_NAME varchar(25) NOT NULL,
postgres(# EMAIL varchar(25) NOT NULL,
postgres(# PHONE_NUMBER varchar(20) DEFAULT NULL,
postgres(# HIRE_DATE date NOT NULL,
postgres(# JOB_ID varchar(10) NOT NULL,
postgres(# SALARY decimal(8,2) DEFAULT NULL,
postgres(# COMMISSION_PCT decimal(2,2) DEFAULT NULL,
postgres(# MANAGER_ID decimal(6,0) DEFAULT NULL,
postgres(# DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
postgres(# FOREIGN KEY(DEPARTMENT_ID)
postgres(# REFERENCES  departments(DEPARTMENT_ID),
postgres(# FOREIGN KEY(JOB_ID)
postgres(# REFERENCES  jobs(JOB_ID)
postgres(# );
CREATE TABLE

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

postgres=# \d employees
                       Table "public.employees"
     Column     |         Type          |            Modifiers
----------------+-----------------------+---------------------------------
 employee_id    | numeric(6,0)          | not null
 first_name     | character varying(20) | default NULL::character varying
 last_name      | character varying(25) | not null
 email          | character varying(25) | not null
 phone_number   | character varying(20) | default NULL::character varying
 hire_date      | date                  | not null
 job_id         | character varying(10) | not null
 salary         | numeric(8,2)          | default NULL::numeric
 commission_pct | numeric(2,2)          | default NULL::numeric
 manager_id     | numeric(6,0)          | default NULL::numeric
 department_id  | numeric(4,0)          | default NULL::numeric
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
    "employees_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(department_id)
    "employees_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 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.
Next: Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, job_id, salary 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, referenced by the column job_id of jobs table, can contain only those values which exist in the jobs table. The specialty of the statement is that the ON UPDATE CASCADE action allows you to perform the cross-table update and ON DELETE RESTRICT action rejects the deletion. The default action is ON DELETE RESTRICT.