Write a postgre 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

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