ORA-02298: cannot validate (%s.%s) - parent keys not found
What does it mean?
You are trying to enable a foreign key constraint. The child table (the one that you are trying to enable the constraint on) has values in it that aren't in the key table (parent table). It quite often happens when refreshing a subset of tables from a production database to a development database.
SQL> alter table andy.room enable constraint ROOM_LOCATION_FK;
alter table andy.room enable constraint ROOM_LOCATION_FK
*
ERROR at line 1:
ORA-02298: cannot validate (ANDY.ROOM_LOCATION_FK) - parent keys not found
How do I fix it?
Well, here are three ways you could fix it...
Option 1. Import/refresh the parent tables
I'll make the assumption that this error has occurred during a refresh of a development database. If that is the case, the best thing to do is to re-import the parent tables. It is advisable to perform a fresh export of all of the tables involved using the consistent=y option - just to make sure everything is in sync.
Option 2. Put the missing values into the parent table
You can identify the missing values with this query:
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'
/
The output of this query is another query. Cut and paste the output back into sqlplus. The second query will list all of the values that are missing in the parent table. Obviously, it is only going to give you the values for one column, the column on which the tables are keyed, but it should help you to identify what has gone wrong.
Option 3. Remove the rows in the child table which contain values that are not in the parent table
The final option is to remove the 'extra data' rows from the child table. This is an absolute last resort - and I would only ever use it in a dev/test database. Take a quick export of the table in its current state, just so you've got something to go back to.
Run the following query. It is similar to the query used in option 2 except that it produces delete statements for the extra rows. Spool the output to a file, or cut and paste it back into sqlplus. Once you've completed the delete, do a commit, and then try to re-enable the constraint again.
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'
/
There are some other useful constraint queries here.
|
