Foreign Key Violations

Missing corresponding rows in another table
  1. Similar to finding rows that fall through a join
  2. Master-detail deletion

Four commonly used methods:

  1. ROWID IS NULL with outer join
     where b.xid(+) = a.xid and b.rowid is null
  2. Correlated subquery predicate to NOT EXISTS
     where not exists
     (select 'x' from b where b.xid = a.xid)
  3. Non-correlated subquery
     where a.xid not in (select b.xid from b)
  4. MINUS with compound query
     select a.xid from a
     select b.xid from b

TOC Top Next