PostgreSQL Create Table: Create a table which will reject any deletion and any updates

19. 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 DELETE NO ACTION and the ON UPDATE NO ACTION actions will reject the deletion and any updates.

Assume that the following is the structure of two table jobs.

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID INTEGER NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT NULL
);

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

Sample Solution:

Code:

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

CREATE TABLE 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.
    JOB_ID INTEGER NOT NULL, -- Defines a column 'JOB_ID' to store job IDs as integers. 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.
    FOREIGN KEY(JOB_ID) REFERENCES jobs(JOB_ID) ON DELETE NO ACTION ON UPDATE NO ACTION -- Defines a foreign key constraint on the 'JOB_ID' column, referencing the 'JOB_ID' column in the 'jobs' table. Specifies no action on both delete and update.
);

Explanation:

• The CREATE TABLE statement creates a new table named 'employees' 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' and 'LAST_NAME' 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' column.
• INTEGER indicates a whole number data type 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.
• The DEFAULT NULL constraint specifies that if no value is provided during insertion, the 'FIRST_NAME' and 'SALARY' columns default to NULL.
• The FOREIGN KEY constraint establishes a relationship between the 'JOB_ID' column in the 'employees' table and the 'JOB_ID' column in the 'jobs' table.
• ON DELETE NO ACTION specifies that no action will be taken if a referenced row in the 'jobs' table is deleted.
• ON UPDATE NO ACTION specifies that no action will be taken if the referenced key (in the 'jobs' table) is updated.

Output:

postgres=# CREATE TABLE  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(# JOB_ID INTEGER,
postgres(# SALARY decimal(8,2) DEFAULT NULL,
postgres(# FOREIGN KEY(JOB_ID)
postgres(# REFERENCES  jobs(JOB_ID)
postgres(# ON DELETE NO ACTION
postgres(# ON UPDATE NO ACTION
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
 job_id      | integer               |
 salary      | numeric(8,2)          | default NULL::numeric
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
Foreign-key constraints:
    "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 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 DELETE SET NULL action will set the foreign key column values in the child table(employees) to NULL when the record in the parent table(jobs) is deleted, with a condition that the foreign key column in the child table must accept NULL values and the ON UPDATE SET NULL action resets the values in the rows in the child table(employees) to NULL values when the rows in the parent table(jobs) are updated.
Next: PostgreSQL Alter Table - Exercises, Practice, Solution