Re: Integrity constraint

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Thu, 05 Feb 2009 19:05:22 -0600
Message-ID: <o3Mil.16157$yr3.3074_at_nlpi068.nbdc.sbc.com>

vm22 wrote:

> On Feb 4, 6:54 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:

>> 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?
>> Very simple here. You are deleting something in X that must exist
>> because it is referenced on X by Y_FK.
>>
>> Example:
>>
>> In X:
>> 1
>> 2
>> 3
>> 4
>>
>> in Y
>> a,b,c,1
>> a,d,e,2
>> a,b,c,3
>>
>> if you delete 3 in X and it is a FK for Y (MUST exist) then you will get
>> this error.
>>
>> It is called REFERENTIAL INTEGRITY.

>
[TOP POSTING FIXED]
 > I have a NOT IN clause.
 >

there is obviously something Oracle THINKS that your delete will violate the constraint.

[just read your other post that you are adding data at the same time you are trying to run this delete]

is ID a unique column?

what is the result of

I generally test my deletes first to ensure I know what I am deleting (having to recover a db because of an error in my script is NOT much fun.)

select * from x
WHERE id NOT IN (SELECT id
FROM y)
AND id NOT IN (SELECT id
FROM z); Received on Thu Feb 05 2009 - 19:05:22 CST