ORA-02298 - parent keys not found

Written By askMLabs on Wednesday, September 22, 2010 | 6:34 AM

SQL> sho user
USER is "SH"
SQL> ALTER TABLE emp_det ADD CONSTRAINT fk_emp_det FOREIGN KEY ( name ) REFERENCES emp( name );
ALTER TABLE emp_det ADD CONSTRAINT fk_emp_det FOREIGN KEY ( name ) REFERENCES emp( name )
*
ERROR at line 1:
ORA-02298: cannot validate (SH.FK_EMP_DET) - parent keys not found

SQL>

In the above example, we are trying to create a foreign constraint on the table emp_det referencing column name in the table emp. But the error shows that there are some emp records exists in emp_det table for which there is no master entries in the table EMP.

So when ever we are creating foreign constraint on the detailed table(emp_det), all the column values which are referencing the master table should exist in the master table.

Troubleshooting

SQL> select unique name from emp_det ed
2 where ed.name is not null and not exists
3 (select null from emp e where e.name = ed.name);

NAME
--------------------------------
John
Marry
Farah

SQL>

SQL> select count(1) from emp_det where name in ('JOHN','MARRY','FARAH');

COUNT(1)
----------
162

SQL> select count(1) from emp where name in ('JOHN','MARRY','FARAH');

COUNT(1)
----------
0

SQL>

WorkAround

1) Need to delete the rows in the table emp_det table corresponding to the above specified Names
or
2) Insert the data in the table emp corresponding to the above specified Names

Hope it helps

SRI

Share this article :

askMLabs