Oracle Technologies Blog

By ASKM

ORA-02298 – parent keys not found

Posted by Srikrishna Murthy Annam on September 22, 2010

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: