Re: Integrity constraint
From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: Tue, 3 Feb 2009 06:24:12 -0800 (PST)
Message-ID: <f927f1e9-9007-4791-b864-3d06c0a5d571_at_v39g2000pro.googlegroups.com>
On Feb 3, 9:39 am, vm22 <vivekmarw..._at_gmail.com> wrote:
> Hi,
>
> I have a developer who has written the following code to delete some
> records:
>
> 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?
Date: Tue, 3 Feb 2009 06:24:12 -0800 (PST)
Message-ID: <f927f1e9-9007-4791-b864-3d06c0a5d571_at_v39g2000pro.googlegroups.com>
On Feb 3, 9:39 am, vm22 <vivekmarw..._at_gmail.com> wrote:
> Hi,
>
> I have a developer who has written the following code to delete some
> records:
>
> 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
Regards,
Jaap.
Received on Tue Feb 03 2009 - 08:24:12 CST