Re: Integrity constraint
Date: Tue, 3 Feb 2009 18:01:50 -0000
"Jaap W. van Dijk" <j.w.vandijk_at_hetnet.nl> wrote in message
.> 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.htmlReceived on Tue Feb 03 2009 - 12:01:50 CST