Re: Integrity constraint

"Jaap W. van Dijk" <j.w.vandijk_at_hetnet.nl> wrote in message news:f927f1e9-9007-4791-b864->
.> DELETE FROM x
.> WHERE id NOT IN (SELECT id
.> FROM y)
.> AND id NOT IN (SELECT id
.> FROM z);
.>
.> ORA-02292: integrity constraint (Y_FK) violated - child record found
.>
.> When there is already a clause in my delete to state do not delete
.> records that are found in table Y, why do I get the integrity
.> constraint error message?.
.

Can id be null in y and z? In that case you are comparing id in x with
.a set that contains a null. For an id in x that is different from the
.not null id's in y and z, the NOT IN condition will yield a UNKNOWN
.and not a TRUE.
.
.Solution: extend SELECT id FROM y/z with AND id IS NOT NULL

If Y or Z had a null for id in any row then the delete statement would always find no rows to delete - so this particular problem would not arise.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html