PostgreSQL Insert Record: Insert rows into a table to ensure that a set of columns contains a unique value that must have existed in the referencing table

Last update on March 20 2024 13:09:10 (UTC/GMT +8 hours)

12. Write a SQL statement to insert rows into the table employees in which a set of columns department_id and manager_id contains a unique value and that combined value must have existed into the table departments.

Sample Solution:

Code:

Here is the code to create a sample table departments :

-- This SQL statement creates a new table named 'departments' with specified columns and constraints.

CREATE TABLE departments ( 
    DEPARTMENT_ID integer NOT NULL UNIQUE,     -- Defines the DEPARTMENT_ID column as an integer type, which cannot be NULL and must be unique
    DEPARTMENT_NAME varchar(30) NOT NULL,      -- Defines the DEPARTMENT_NAME column as a varchar type with a maximum length of 30 characters, which cannot be NULL
    MANAGER_ID integer DEFAULT NULL,           -- Defines the MANAGER_ID column as an integer type with a default value of NULL
    LOCATION_ID integer DEFAULT NULL,          -- Defines the LOCATION_ID column as an integer type with a default value of NULL
    PRIMARY KEY (DEPARTMENT_ID,MANAGER_ID)     -- Defines a composite primary key constraint on the DEPARTMENT_ID and MANAGER_ID columns
);

Explanation:

The CREATE TABLE statement is used to create a new table in the database.
departments is the name of the new table being created.
(DEPARTMENT_ID integer NOT NULL UNIQUE, DEPARTMENT_NAME varchar(30) NOT NULL, MANAGER_ID integer DEFAULT NULL, LOCATION_ID integer DEFAULT NULL) specifies the columns of the table along with their data types and constraints.
DEPARTMENT_ID is defined as an integer type column that cannot contain NULL values (NOT NULL) and must have unique values (UNIQUE constraint).
DEPARTMENT_NAME is defined as a varchar type column with a maximum length of 30 characters and cannot contain NULL values (NOT NULL).
MANAGER_ID is defined as an integer type column with a default value of NULL. This means that if no value is provided during insertion, the column will default to NULL.
LOCATION_ID is defined as an integer type column with a default value of NULL. This means that if no value is provided during insertion, the column will default to NULL.
PRIMARY KEY (DEPARTMENT_ID,MANAGER_ID) defines a composite primary key constraint on the combination of the DEPARTMENT_ID and MANAGER_ID columns. This ensures that each combination of department and manager is unique within the table.

Now input three rows into the table departments:

-- This SQL statement inserts new rows into the 'departments' table with specified values for DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID columns.

INSERT INTO departments VALUES(60,'SALES',201,89);     -- Inserts a row with DEPARTMENT_ID=60, DEPARTMENT_NAME='SALES', MANAGER_ID=201, and LOCATION_ID=89
INSERT INTO departments VALUES(61,'ACCOUNTS',201,89);  -- Inserts a row with DEPARTMENT_ID=61, DEPARTMENT_NAME='ACCOUNTS', MANAGER_ID=201, and LOCATION_ID=89
INSERT INTO departments VALUES(80,'FINANCE',211,90);   -- Inserts a row with DEPARTMENT_ID=80, DEPARTMENT_NAME='FINANCE', MANAGER_ID=211, and LOCATION_ID=90

Explanation:

The INSERT INTO statement is used to add new rows into a table.
departments is the name of the table where the new rows will be inserted.
Each INSERT INTO statement specifies values for each column in the order they are defined in the table schema.
For the first INSERT INTO statement:

DEPARTMENT_ID is set to 60, DEPARTMENT_NAME is set to 'SALES', MANAGER_ID is set to 201, and LOCATION_ID is set to 89.

For the second INSERT INTO statement:

DEPARTMENT_ID is set to 61, DEPARTMENT_NAME is set to 'ACCOUNTS', MANAGER_ID is set to 201, and LOCATION_ID is set to 89.

For the third INSERT INTO statement:

DEPARTMENT_ID is set to 80, DEPARTMENT_NAME is set to 'FINANCE', MANAGER_ID is set to 211, and LOCATION_ID is set to 90.

These statements insert records into the 'departments' table, each representing a different department with its respective details.

Here is the command to see the list of inserted rows :

postgres=# SELECT * FROM departments;
 department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
            60 | SALES           |        201 |          89
            61 | ACCOUNTS        |        201 |          89
            80 | FINANCE         |        211 |          90
(3 rows)

Here is another table employees :

-- This SQL statement creates a new table named 'employees' with specified columns and constraints.

CREATE TABLE employees ( 
    EMPLOYEE_ID integer NOT NULL PRIMARY KEY,    -- Defines the EMPLOYEE_ID column as an integer type, which cannot be NULL and serves as the primary key
    FIRST_NAME varchar(20) DEFAULT NULL,        -- Defines the FIRST_NAME column as a varchar type with a maximum length of 20 characters, which can have a default value of NULL
    LAST_NAME varchar(25) NOT NULL,             -- Defines the LAST_NAME column as a varchar type with a maximum length of 25 characters, which cannot be NULL
    JOB_ID varchar(10) NOT NULL,                -- Defines the JOB_ID column as a varchar type with a maximum length of 10 characters, which cannot be NULL
    SALARY decimal(8,2) DEFAULT NULL,           -- Defines the SALARY column as a decimal type with a precision of 8 digits and 2 decimal places, which can have a default value of NULL
    MANAGER_ID integer DEFAULT NULL,             -- Defines the MANAGER_ID column as an integer type with a default value of NULL
    DEPARTMENT_ID integer DEFAULT NULL,          -- Defines the DEPARTMENT_ID column as an integer type with a default value of NULL
    FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID)       -- Defines a foreign key constraint on the combination of DEPARTMENT_ID and MANAGER_ID columns, referencing the 'departments' table
    REFERENCES departments(DEPARTMENT_ID,MANAGER_ID)
);

Explanation:

The CREATE TABLE statement is used to create a new table in the database.
employees is the name of the new table being created.
(EMPLOYEE_ID integer NOT NULL PRIMARY KEY, FIRST_NAME varchar(20) DEFAULT NULL, LAST_NAME varchar(25) NOT NULL, JOB_ID varchar(10) NOT NULL, SALARY decimal(8,2) DEFAULT NULL, MANAGER_ID integer DEFAULT NULL, DEPARTMENT_ID integer DEFAULT NULL) specifies the columns of the table along with their data types and constraints.
EMPLOYEE_ID is defined as an integer type column that cannot contain NULL values (NOT NULL) and serves as the primary key of the table (PRIMARY KEY constraint).
FIRST_NAME is defined as a varchar type column with a maximum length of 20 characters and can have a default value of NULL (DEFAULT NULL).
LAST_NAME is defined as a varchar type column with a maximum length of 25 characters and cannot contain NULL values (NOT NULL).
JOB_ID is defined as a varchar type column with a maximum length of 10 characters and cannot contain NULL values (NOT NULL).
SALARY is defined as a decimal type column with a precision of 8 digits and 2 decimal places and can have a default value of NULL (DEFAULT NULL).
MANAGER_ID is defined as an integer type column with a default value of NULL (DEFAULT NULL).
DEPARTMENT_ID is defined as an integer type column with a default value of NULL (DEFAULT NULL).
FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID) REFERENCES departments(DEPARTMENT_ID,MANAGER_ID) defines a foreign key constraint on the combination of DEPARTMENT_ID and MANAGER_ID columns, referencing the DEPARTMENT_ID and MANAGER_ID columns in the departments table. This ensures that the values inserted into the DEPARTMENT_ID and MANAGER_ID columns in the employees table must exist as a pair in the departments table.

Now insert the rows in the table employees.

INSERT INTO employees VALUES(510,'Alex','Hanes','CLERK',18000,201,60);
INSERT INTO employees VALUES(511,'Kim','Leon','CLERK',18000,211,80);

Here is the command to see the list of inserted rows :

postgres=# SELECT * FROM employees;
 employee_id | first_name | last_name | job_id |  salary  | manager_id | department_id
-------------+------------+-----------+--------+----------+------------+---------------
         510 | Alex       | Hanes     | CLERK  | 18000.00 |        201 |            60
         511 | Kim        | Leon      | CLERK  | 18000.00 |        211 |            80
(2 rows)

The value against department_id and manager_id combination (60,201) and (80,211) are unique in the departmentis(parent) table so, there is no problem arise to insert the rows in the child table employees.

Now insert another row in the employees table :


INSERT INTO employees VALUES(512,'Kim','Leon','CLERK',18000,80,211);

Now see the output :

postgres=# INSERT INTO employees VALUES(512,'Kim','Leon','CLERK',18000,80,211);
ERROR:  insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL:  Key (department_id, manager_id)=(211, 80) is not present in table "departments".

Here in the above, the value against department_id and manager_id combination (211,80) does not matching with the same combination in departments(parent table) table and that is why the child table employees can not contain the combination of values including department_id and manager_id as specified. Here the primary key - foreign key relationship is being violated and shows the above message.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to insert rows into the job_history table in which one column job_id is containing those values which exist in job_id column of jobs table.
Next: Write a SQL statement to insert rows into the table employees in which a set of columns department_id and job_id contains the values which must have existed into the table departments and jobs.