Oracle SQL Developer: Integrity Constraint Violated (Parent Key Not Found) while inserting values

Question

So the original question I was asked to solve was this:

Create two tables: Employee: empID (PK), empFname, empLname, deptID(FK) and Department: deptID(PK), deptName, chairID. chairID is empID from Employee table. Insert at least 3 rows in the Department table and at least 6 rows in the Employee table. Create trigger on update of chairID that enforces the following business rules: One employee can chair no more than one department. Each department has exactly one chair.

My solution for the problem was this:

create table Employee(empID NUMBER,
empFname VARCHAR2(20),
empLname VARCHAR2(20),
deptID NUMBER,
PRIMARY KEY (empID)
);

create table Department(deptID NUMBER,
deptName VARCHAR2(20),
chairID NUMBER,
PRIMARY KEY (deptID));

ALTER TABLE Employee
ADD FOREIGN KEY (deptID) REFERENCES Department(deptID);  

ALTER TABLE Department
ADD FOREIGN KEY (chairID) REFERENCES Employee(empID);   


INSERT INTO DEPARTMENT (deptID ,deptName,chairID ) VALUES (401,'COMPUTER SCIENCE',301);
INSERT INTO DEPARTMENT (deptID ,deptName,chairID ) VALUES (402,'ELECTRONICS',302);
INSERT INTO DEPARTMENT (deptID ,deptName,chairID ) VALUES (403,'MATHEMATICS',303);

INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1001,'Alen','Zer',301);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1002,'Beny','Ker',301);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1003,'Clen','Ler',302);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1004,'Dlen','Mer',302);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1005,'Elen','Ner',303);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1006,'Flen','Oer',303);

CREATE OR REPLACE TRIGGER chairID_after_update
BEFORE UPDATE
ON Department
FOR EACH ROW

DECLARE
v_username varchar2(10);
v_count number;
v_sql varchar2(50);
BEGIN

select count(*) into v_count from Department where chairID = :new.chairID;
IF v_count = 0 then
v_sql := 'alter table set chairID ='+ :new.chairID;
execute immediate v_sql;
end if;

END;
/

Both my table creates and trigger compile, but upon trying to insert into my tables, all of my inserts statements hit:

SQL Error: ORA-02291: integrity constraint (SYSTEM.SYS_C0010675) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.

What should I do to fix this issue? Any help/suggestions would be appreciated.

Answer 1

One employee can chair no more than one department.

Each department has exactly one chair.

No need for triggers (apart from for educational purposes) - make the DEPARTMENT.CHAIRID column UNIQUE and NOT NULL.

CREATE TABLE Employee(
  empID    NUMBER
           CONSTRAINT employee__empid__pk PRIMARY KEY,
  empFname VARCHAR2(20),
  empLname VARCHAR2(20),
  deptID   NUMBER       
);

CREATE TABLE Department(
  deptID   NUMBER
           CONSTRAINT department__deptid__pk PRIMARY KEY,
  deptName VARCHAR2(20),
  chairID  NUMBER
           CONSTRAINT department__chairid__nn NOT NULL
           CONSTRAINT department__chairid__u  UNIQUE
           CONSTRAINT department__chairid__fk REFERENCES employee ( empID )
);

ALTER TABLE Employee
ADD CONSTRAINT employee__deptid__fk
    FOREIGN KEY (deptID) REFERENCES Department(deptID);

Insert the employees with NULL department ids, then create the departments and, finally, update the employees to include the department ids:

INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID)
  SELECT 1001,'Alen','Zer', NULL FROM DUAL UNION ALL
  SELECT 1002,'Beny','Ker', NULL FROM DUAL UNION ALL
  SELECT 1003,'Clen','Ler', NULL FROM DUAL UNION ALL
  SELECT 1004,'Dlen','Mer', NULL FROM DUAL UNION ALL
  SELECT 1005,'Elen','Ner', NULL FROM DUAL UNION ALL
  SELECT 1006,'Flen','Per', NULL FROM DUAL;

INSERT INTO DEPARTMENT (deptID ,deptName,chairID )
  SELECT 301,'COMPUTER SCIENCE', 1001 FROM DUAL UNION ALL
  SELECT 302,'ELECTRONICS',      1004 FROM DUAL UNION ALL
  SELECT 303,'MATHEMATICS',      1005 FROM DUAL;

MERGE INTO EMLPOYEE dst
USING ( SELECT 301 AS deptid, 1001 AS empID FROM DUAL UNION ALL
        SELECT 301, 1002 FROM DUAL UNION ALL
        SELECT 302, 1003 FROM DUAL UNION ALL
        SELECT 302, 1004 FROM DUAL UNION ALL
        SELECT 303, 1005 FROM DUAL UNION ALL
        SELECT 303, 1006 FROM DUAL ) src
ON ( dst.empId = src.empid )
WHEN MATCHED THEN
  UPDATE SET deptId = src.deptID;

Answer 2

Obviously you have a logical problem (circular reasoning): You can't create a department first, when there are no employees, because the foreign key constraint on CHAIRID will fail, and you can't create employees first, because the foreign key on DEPTID will fail. Yet your data model is, obviously, correct.

So, what to do?

One solution is to not add the foreign keys right at the start. Do everything else, insert data, and only then alter the tables to add the foreign keys.

A better solution is to add the FK from the start, but make them deferrable and initially deferred. Then insert data, and then enable the FK constraints. (Actually it is a good idea to make the FK constraints deferrable, because you may have similar problems in the future, and you will appreciate the flexibility.)

The best solution, though, is to create the tables with all the constraints just like you did. (Do make the FK constraints deferrable though). Then INSERT into DEPARTMENTS, but with all the CHAIRID left NULL. Then insert the EMPLOYEE rows, and then finally UPDATE the DEPARTMENTS rows to show the proper CHAIRID for each department.

The point being, FK columns DO allow NULL - take advantage of that to break the vicious cycle (the circular logic).

Now - your requirement is that each department should have exactly one chair... you can make the column CHAIRID non-nullable. This again will get in the way of the plan I described. Add that constraint (and certainly the trigger) only after the INSERT and UPDATE statements.