Inserting data into tables with referential constraints

If you are inserting data into a parent table with a parent key, SQL does not allow:

• Duplicate values for the parent key
• If the parent key is a primary key, a null value for any column of the primary key

If you are inserting data into a dependent table with foreign keys:

• Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table.
• If any column in the foreign key is null, the entire foreign key is considered null. If all foreign keys that contain the column are null, the INSERT succeeds (as long as there are no unique index violations).

Alter the sample application project table (PROJECT) to define two foreign keys:

• A foreign key on the department number (DEPTNO) which references the department table
• A foreign key on the employee number (RESPEMP) which references the employee table.

  ALTER TABLE CORPDATA.PROJECT ADD CONSTRAINT RESP_DEPT_EXISTS
                FOREIGN KEY (DEPTNO)
                REFERENCES CORPDATA.DEPARTMENT
                ON DELETE RESTRICT
   
  ALTER TABLE CORPDATA.PROJECT ADD CONSTRAINT RESP_EMP_EXISTS
                FOREIGN KEY (RESPEMP)
                REFERENCES CORPDATA.EMPLOYEE
                ON DELETE RESTRICT
   

Notice that the parent table columns are not specified in the REFERENCES clause. The columns are not required to be specified as long as the referenced table has a primary key or eligible unique key which can be used as the parent key.

Every row inserted into the PROJECT table must have a value of DEPTNO that is equal to some value of DEPTNO in the department table. (The null value is not allowed because DEPTNO in the project table is defined as NOT NULL.) The row must also have a value of RESPEMP that is either equal to some value of EMPNO in the employee table or is null.

The following INSERT statement fails because there is no matching DEPTNO value ('A01') in the DEPARTMENT table.

   INSERT INTO  CORPDATA.PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
           VALUES  ('AD3120', 'BENEFITS ADMIN', 'A01', '000010')

Likewise, the following INSERT statement is unsuccessful because there is no EMPNO value of '000011' in the EMPLOYEE table.

   INSERT INTO CORPDATA.PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
           VALUES ('AD3130', 'BILLING', 'D21', '000011')

The following INSERT statement completes successfully because there is a matching DEPTNO value of 'E01' in the DEPARTMENT table and a matching EMPNO value of '000010' in the EMPLOYEE table.

   INSERT INTO CORPDATA.PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
           VALUES ('AD3120', 'BENEFITS ADMIN', 'E01', '000010')