ORA-02298 Parent Keys Not Found?

Question

I've been stuck on this for ages as every time I try it - it comes up with the following error: Cannot Validate - parent keys not found.

Here's the code (http://i.imgur.com/6JBzTiM.jpg):

I can create the Primary Key in the Employees table and assign it to EmployeeId. But when trying to add that as a foreign key in the WorkPackages table (using the code below)

ALTER TABLE WORKPACKAGES
ADD FOREIGN KEY (EMPLOYEEID) REFERENCES EMPLOYEES (EMPLOYEEID);

it keeps on coming up with the validation error.

What am I doing wrong?

Answer 1

ALTER TABLE WORKPACKAGES
ADD FOREIGN KEY (EMPLOYEEID) REFERENCES EMPLOYEES (EMPLOYEEID);

When this key is enforced, Oracle checks that all employeeid present in Workpackages table is present in Employees table.

Your options:

Find the offending keys by running

SELECT employeeid
FROM   workpackages
WHERE  employeeid NOT IN (SELECT employeeid
                          FROM   employees); 

and then insert them into the employee table.

Another option is to use NOVALIDATE so that existing data isn't checked, but any new inserts/updates will be validated. See this fiddle for demo on this.