ORA-02298: cannot validate - parent keys not found
634729Jan 22 2010 — edited Feb 19 2011When 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'
/