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.
Here is the code to create a sample 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 insert two rows in the jobs table.
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 :
job_id | job_title | min_salary | max_salary --------+-----------+------------+------------ 1001 | OFFICER | 8000 | 20000 1002 | CLERK | 8000 | 20000 (2 rows)
Here is another table:
CREATE TABLE job_history ( EMPLOYEE_ID integer NOT NULL PRIMARY KEY, JOB_ID integer NOT NULL, DEPARTMENT_ID integer DEFAULT NULL, FOREIGN KEY (job_id) REFERENCES jobs(job_id) );
Now insert the rows in the job_history table.
INSERT INTO job_history VALUES(501,1001,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.
What is the difficulty level of this exercise?