Adding foreign keys error: ORA-02298: cannot validate - parent keys not found

Question

I essentially have 2 tables, one named table1 and another called table2.

I want to insert a foreign key into table2 and have it make sure it is linked as a parent to table1 (since both tables have the same column called: EMPNO).

This is what I tried:

ALTER TABLE table2 
ADD FOREIGN KEY (EMPNO) 
REFERENCES table1(EMPNO)  

however I receive an error from my live SQL when running it:

ORA-02298: cannot validate (SQL_EOTBMPLTBLKHWFZRYEHITBYIH.) - parent keys not found

Answer 1

This error means that table2 has foreign key values that do not exist in the parent table, table1. To identify them, run a query like this:

SELECT DISTINCT EMPNO FROM table2 WHERE EMPNO NOT IN (
    SELECT EMPNO FROM table1)

Fix those bad key values and then run your ALTER TABLE again.