Re: Integrity constraint
Date: Tue, 3 Feb 2009 06:24:12 -0800 (PST)
On Feb 3, 9:39 am, vm22 <vivekmarw..._at_gmail.com> wrote:
> I have a developer who has written the following code to delete some
> 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
Jaap. Received on Tue Feb 03 2009 - 08:24:12 CST