Tags: child, database, foreign, key, mysql, oracle, orphaned, par_col1, par_col2, parent, pk_col, primary, rows, sql, suppose, table, table_a, table_b
finding orphaned child rows
Suppose I have Table_A as a parent table with primary key pk_col(par_col1,par_col2)
It's child table is Table_B with foreign key fk_col(chd_col1,chd_col2) referencing pk_col of Table_A(par_col1, par_col2)
I have orphaned rows in Table_B for which the constraint fk_col was enabled with the NOVALIDATE clause.
Is there a way by query that I can find out all the rows in Table_B that do not have a parent in Table_A ?
Leave a comment...
- 1 Comments
select chd_col1,chd_col2from table_Bwhere not exists(select 1 from table_A where chd_col1= par_col1 and chd_col2=par_col2);
Another option is to recreate the constraint with the EXCEPTIONS INTO clause#1; Fri, 22 Feb 2008 23:22:00 GMT