PostgreSQL Insert Record: Insert rows in a table to ensure that the value entered in a specific column must exist in the referencing table

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

Sample Solution:

Code:

Here is the code to create a sample 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, also serves as the primary key
    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, with 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, with 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 is defined as an integer type column, which cannot be NULL (NOT NULL) and must be unique (UNIQUE). Additionally, it 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). It also has a default value of ' ' (a single space), meaning if no value is provided during insertion, a space will be inserted by default.
• MIN_SALARY is defined as a decimal type column with a precision of 6 digits and 0 decimal places, with a default value of 8000.
• MAX_SALARY is defined as a decimal type column with a precision of 6 digits and 0 decimal places, with a default value of 20000.

Now insert two rows in the jobs table.

-- This SQL statement inserts new rows into the 'jobs' table with specified values for the 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.
• (JOB_ID,JOB_TITLE) specifies the columns into which the values will be inserted.
• VALUES(1001,'OFFICER') and VALUES(1002,'CLERK') provide the values to be inserted into the specified columns.
• These statements insert rows with specific JOB_IDs and JOB_TITLEs into the 'jobs' table.

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

job_id | job_title | min_salary | max_salary
--------+-----------+------------+------------
   1001 | OFFICER   |       8000 |      20000
   1002 | CLERK     |       8000 |      20000
(2 rows)

Here is another table:

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

CREATE TABLE job_history ( 
    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
    JOB_ID integer NOT NULL,                    -- Defines the JOB_ID column as an integer type, which cannot be NULL
    DEPARTMENT_ID integer DEFAULT NULL,         -- Defines the DEPARTMENT_ID column as an integer type with a default value of NULL
    FOREIGN KEY (job_id) REFERENCES jobs(job_id)  -- Defines a foreign key constraint on the JOB_ID column referencing the 'jobs' table
);

Explanation:

• The CREATE TABLE statement is used to create a new table in the database.
• job_history is the name of the new table being created.
• EMPLOYEE_ID is defined as an integer type column, which cannot be NULL (NOT NULL) and serves as the primary key of the table (PRIMARY KEY constraint).
• JOB_ID is defined as an integer type column, which cannot be NULL (NOT NULL).
• DEPARTMENT_ID is defined as an integer type column with a default value of NULL (DEFAULT NULL). This means that if no value is provided during insertion, the column will default to NULL.
• FOREIGN KEY (job_id) REFERENCES jobs(job_id) defines a foreign key constraint on the JOB_ID column, which references the job_id column in the jobs table. This ensures that the values inserted into the JOB_ID column in the job_history table must exist in the jobs table.

Now insert the rows in the job_history table.

-- This SQL statement inserts a new row into the 'job_history' table with specified values for EMPLOYEE_ID, JOB_ID, and DEPARTMENT_ID columns.

INSERT INTO job_history VALUES(501,1001,60); -- Inserts a row with EMPLOYEE_ID=501, JOB_ID=1001, and DEPARTMENT_ID=60

Explanation:

• The INSERT INTO statement is used to add a new row into a table.
• job_history is the name of the table where the new row will be inserted.
• The statement specifies values for each column in the table in the order they are defined in the table schema.
• EMPLOYEE_ID is set to 501, JOB_ID is set to 1001, and DEPARTMENT_ID is set to 60 for the new row being inserted.
• This statement inserts a record into the 'job_history' table, indicating that employee with ID 501 had a job with ID 1001 and was in department 60.

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

postgres=# SELECT  * FROM job_history;
 employee_id | job_id | department_id
-------------+--------+---------------
         501 |   1001 |            60
(1 row)

The value against job_id is 1001 which is exists in the job_id column of the jobs table, so no problem arise.

Now insert another row in the job_history table.

INSERT INTO job_history VALUES(502,1003,80);

Here is the output:

postgres=# INSERT INTO job_history VALUES(502,1003,80);
ERROR:  insert or update on table "job_history" violates foreign key constraint "job_history_job_id_fkey"
DETAIL:  Key (job_id)=(1003) is not present in table "jobs".

Here in the above, the value against job_id is 1003 which is not exists in the job_id column of the jobs(parent table) table and that is why the child table job_history can not contain the value of job_id as specified. Here the primary key - foreign key relationship is violating 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 records into the table countries to ensure that the country_id column will not contain any duplicate data and this will be automatically incremented and the column country_name will be filled up by 'N/A' if no value assigned to that column.
Next: 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.