Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-02298: cannot validate - parent keys not found

634729Jan 22 2010 — edited Feb 19 2011
When I attempt to use alter table and enable one of the previously disabled foreign key constraints I get an ORA-02298. I've used a number of queries in an attempt to see what is missing so I can try and fix it. Each of my queries are coming back with no rows selected. More specifically I'm attempting to use the solutions from this website http://www.shutdownabort.com/errors/ORA-02298.php
Why would the queries keep coming back with no rows selected? If Option 2 and Option 3 both come back with no rows selected shouldn't I be able to enable the constraint? My assumption is that I'm using the queries incorrectly but I don't know how I am doing it wrong.

Here are the queries copied and pasted from the previously mentioned website.
Option 2
-----
select 'select '||cc.column_name-
||' from '||c.owner||'.'||c.table_name-
||' a where not exists (select ''x'' from '-
||r.owner||'.'||r.table_name-
||' where '||rc.column_name||' = a.'||cc.column_name||')'
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.constraint_type = 'R'
and c.owner not in ('SYS','SYSTEM')
and c.r_owner = r.owner
and c.owner = cc.owner
and r.owner = rc.owner
and c.constraint_name = cc.constraint_name
and r.constraint_name = rc.constraint_name
and c.r_constraint_name = r.constraint_name
and cc.position = rc.position
and c.owner = '&table_owner'
and c.table_name = '&table_name'
and c.constraint_name = '&constraint_name'
/



Option 3
-----
select 'delete from '-
||c.owner||'.'||c.table_name-
||' a where not exists (select ''x'' from '-
||r.owner||'.'||r.table_name-
||' where '||rc.column_name||' = a.'||cc.column_name||')'
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.constraint_type = 'R'
and c.owner not in ('SYS','SYSTEM')
and c.r_owner = r.owner
and c.owner = cc.owner
and r.owner = rc.owner
and c.constraint_name = cc.constraint_name
and r.constraint_name = rc.constraint_name
and c.r_constraint_name = r.constraint_name
and cc.position = rc.position
and c.owner = '&table_owner'
and c.table_name = '&table_name'
and c.constraint_name = '&constraint_name'
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2011
Added on Jan 22 2010
21 comments
64,574 views