Re: Integrity constraint

From: <xhoster_at_gmail.com>
Date: 03 Feb 2009 17:06:40 GMT
Message-ID: <20090203121053.135$9s_at_newsreader.com>

"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

-- 
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

Received on Tue Feb 03 2009 - 11:06:40 CST