Oracle 11g: Confounding constraint
There is an Oracle built-in solution for that. You could use the EXCEPTIONS clause of the ALTER TABLE:
-- parent table create table t1 (col1 number primary key); insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); commit; -- child table create table t2 (col1 number); insert into t2 values (1); insert into t2 values (2); insert into t2 values (3); insert into t2 values (4); -- bad data commit; -- You create a table for the exceptions create table excepts (row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30)); -- you still get error alter table t2 add constraint f2 foreign key (col1) references t1 exceptions into excepts ; -- but bad data will be here -- please notice its 'ROW_ID' from the second table select t2.* from t2, excepts where t2.rowid = excepts.row_id;