Re: Integrity constraint

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 03 Feb 2009 12:46:32 +0100
Message-ID: <49882ea0$0$192$e4fe514c_at_news.xs4all.nl>

Michael Rauscher schreef:
> vm22 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?

>
> Because a corresponding record exists in table Z?
>
> Hint: De Morgan's laws
>
> Bye
> Michael
>
>
>
>

Is there a column named ID in y? If not, the statement will not fail with any syntax errors, but always find corresponding records in y. To check this, change your statement to:

DELETE FROM x
WHERE x.id NOT IN (SELECT y.id
FROM y)
AND x.id NOT IN (SELECT z.id
FROM z);

If there's no column id in y, you'll get a syntax error.

Other possibility: Y_FK is not on column ID... can't see that from here.

Shakespeare Received on Tue Feb 03 2009 - 05:46:32 CST