PostgreSQL Insert Record: Insert rows into a table to ensure that a set of columns contains the values which must have existed into the referencing two tables

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

13. 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.

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)               -- Defines DEPARTMENT_ID as the primary key of the table
);

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 (DEFAULT NULL).
LOCATION_ID is defined as an integer type column with a default value of NULL (DEFAULT NULL).
PRIMARY KEY (DEPARTMENT_ID) defines DEPARTMENT_ID as the primary key of the table, ensuring that each department ID is unique and serves as the primary means of identifying records in the table.

Now input two 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

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.

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
(2 rows)

Here is another table jobs:

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

CREATE TABLE jobs ( 
    JOB_ID integer NOT NULL UNIQUE PRIMARY KEY,    -- Defines the JOB_ID column as an integer type, which cannot be NULL and must be unique, serving as the primary key of the table
    JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',    -- Defines the JOB_TITLE column as a varchar type with a maximum length of 35 characters, which cannot be NULL and has a default value of ' '
    MIN_SALARY decimal(6,0) DEFAULT 8000,         -- Defines the MIN_SALARY column as a decimal type with a precision of 6 digits and 0 decimal places, which has a default value of 8000
    MAX_SALARY decimal(6,0) DEFAULT 20000          -- Defines the MAX_SALARY column as a decimal type with a precision of 6 digits and 0 decimal places, which has a default value of 20000
);

Explanation:

The CREATE TABLE statement is used to create a new table in the database.
jobs is the name of the new table being created.
(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 20000) specifies the columns of the table along with their data types and constraints.
JOB_ID is defined as an integer type column that cannot contain NULL values (NOT NULL), must be unique (UNIQUE constraint), and serves as the primary key of the table (PRIMARY KEY constraint).
JOB_TITLE is defined as a varchar type column with a maximum length of 35 characters, which cannot be NULL (NOT NULL), and has a default value of ' ' if not specified (DEFAULT ' ').
MIN_SALARY is defined as a decimal type column with a precision of 6 digits and 0 decimal places, which has a default value of 8000 if not specified (DEFAULT 8000).
MAX_SALARY is defined as a decimal type column with a precision of 6 digits and 0 decimal places, which has a default value of 20000 if not specified (DEFAULT 20000).

Now input two rows into the table departments:

-- This SQL statement inserts new rows into the 'jobs' table with specified values for JOB_ID and JOB_TITLE columns.

INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1001,'OFFICER');   -- Inserts a row with JOB_ID=1001 and JOB_TITLE='OFFICER'
INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1002,'CLERK');     -- Inserts a row with JOB_ID=1002 and JOB_TITLE='CLERK'

Explanation:

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

JOB_ID is set to 1001 and JOB_TITLE is set to 'OFFICER'.

For the second INSERT INTO statement:

JOB_ID is set to 1002 and JOB_TITLE is set to 'CLERK'.

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

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

postgres=# SELECT * FROM jobs;
 job_id | job_title | min_salary | max_salary
--------+-----------+------------+------------
   1001 | OFFICER   |       8000 |      20000
   1002 | CLERK     |       8000 |      20000
(2 rows)

Here is another table employees :

CREATE TABLE employees ( 
EMPLOYEE_ID integer NOT NULL PRIMARY KEY, 
FIRST_NAME varchar(20) DEFAULT NULL, 
LAST_NAME varchar(25) NOT NULL, 
DEPARTMENT_ID integer DEFAULT NULL, 
FOREIGN KEY(DEPARTMENT_ID) 
REFERENCES  departments(DEPARTMENT_ID),
JOB_ID integer NOT NULL, 
FOREIGN KEY(JOB_ID) 
REFERENCES  jobs(JOB_ID),
SALARY decimal(8,2) DEFAULT NULL
);

Now insert the rows into the table employees.

INSERT INTO employees VALUES(510,'Alex','Hanes',60,1001,18000);

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

postgres=# SELECT * FROM employees;
 employee_id | first_name | last_name | department_id | job_id |  salary
-------------+------------+-----------+---------------+--------+----------
         510 | Alex       | Hanes     |            60 |   1001 | 18000.00
(1 row)

Here in the above insert statement the child column department_id and job_id of child table employees are successfully referencing with the department_id and job_id column of parent tables departments and jobs respectively, so no problem have been arisen to the insertion.

Now insert another row in the employees table.

INSERT INTO employees VALUES(511,'Tom','Elan',60,1003,22000);

Now see the output :

postgres=# INSERT INTO employees VALUES(511,'Tom','Elan',60,1003,22000);
ERROR:  insert or update on table "employees" violates foreign key constraint "employees_job_id_fkey"
DETAIL:  Key (job_id)=(1003) is not present in table "jobs".

Here in the above insert statement show that, within child columns department_id and job_id of child table employees, the department_id are successfully referencing with the department_id of parent table departments but job_id column are not successfully referencing with the job_id of parent table jobs, so the problem have been arisen to the insertion displayed an error message.

Now insert another row in the employees table.

INSERT INTO employees VALUES(511,'Tom','Elan',80,1001,22000);

Now see the output :

postgres=# INSERT INTO employees VALUES(511,'Tom','Elan',80,1001,22000);
ERROR:  insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL:  Key (department_id)=(80) is not present in table "departments".

Here in the above insert statement show that, within child columns department_id and job_id of child table employees, the job_id are successfully referencing with the job_id of parent table jobs but department_id column are not successfully referencing with the department_id of parent table departments, so the problem have been arisen to the insertion and displayed the error 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: PostgreSQL Basic Simple - Exercises, Practice, Solution