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.
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.
What is the difficulty level of this exercise?