Re: Integrity constraint

From: vm22 <>
Date: Wed, 4 Feb 2009 06:52:04 -0800 (PST)
Message-ID: <>

Thanks Jonathan. We cannot insert a child record as there is a Foreign Key on it.

In response to all the other questions:
Can id be null in y and z? Answer is no - both id columns in table y and z are Foreign Key constraints.
Version of Oracle is
Is it possible to insert child records when the parent is being deleted? No.
Definition of Y_FK:

 REFERENCES X (ID)); Regards,

On Feb 3, 6:08 pm, "Jonathan Lewis" <> wrote:
> "Gerard H. Pille" <> wrote in messagenews:49887dab$0$2870$
> >>>> DELETE FROM x
> >>>> WHERE id NOT IN (SELECT id
> >>>> FROM y)
> >>>> AND id NOT IN (SELECT id
> >>>> FROM z);
> > Is it possible to insert child records when the parent is being deleted?
> It may be possible - but I'd want to set up a very careful test case.
> It all depends on read and write consistency and the amount of time
> it takes to do a bulk delete.
> The delete starts at SCN nnn say, so presumably the
> subqueries used for the NOT IN have to be read consistent to
> SCN nnn.
> But say it takes 60 seconds to work through table X deleting
> all the data, and after 45 seconds a user inserts a row into Y
> that is a child of the last record that ought to have been deleted.
> The parent row in X is not locked and marked as deleted, so
> Y will insert safely, and can be committed.
> The delete reaches the row in X and READ-consistency says
> the row doesn't exist in Y, but when the delete attempt goes
> to CURRENT mode from CONSISTENT mode, the row
> becomes visible and committed - so the delete fails and rolls back.
> Tom Kyte has some examples of 'write consistency' on his AskTom
> site (a couple of years old, now, but you should be able to find
> them with a search).  I don't know if he also worked through this
> type of delete event, though.  In his example a delete without a
> child table could rollback from a write-consistent conflict and
> restart - maybe the presence of the child makes that impossible
> for some reason.
> --
> Regards
> Jonathan Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ
Received on Wed Feb 04 2009 - 08:52:04 CST

Original text of this message