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

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 :

CREATE TABLE departments ( 
DEPARTMENT_ID integer NOT NULL UNIQUE, 
DEPARTMENT_NAME varchar(30) NOT NULL, 
MANAGER_ID integer DEFAULT NULL, 
LOCATION_ID integer DEFAULT NULL, 
PRIMARY KEY (DEPARTMENT_ID) 
);

Now input two rows into the table departments:

INSERT INTO departments VALUES(60,'SALES',201,89);
INSERT INTO departments VALUES(61,'ACCOUNTS',201,89);

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:

CREATE TABLE 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 20000
);

Now input two rows into the table departments:

INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1001,'OFFICER');
INSERT INTO jobs(JOB_ID,JOB_TITLE) VALUES(1002,'CLERK');

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