Re: Integrity constraint

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 03 Feb 2009 18:23:55 +0100
Message-ID: <49887dab$0$2870$ba620e4c_at_news.skynet.be>

xhoster_at_gmail.com schreef:

> "Jaap W. van Dijk" <j.w.vandijk_at_hetnet.nl> wrote:

>> On Feb 3, 9:39=A0am, 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

> 
> Another thought.  What if another process inserts a record into y or z
> after the delete statement was started?  The x is an orphan at the time the
> transaction was started, but is no longer one.  Could that scenario give
> an integrity error rather than a serialization error?
> 
> Xho
> 

Is it possible to insert child records when the parent is being deleted? Received on Tue Feb 03 2009 - 11:23:55 CST