Re: Integrity constraint

From: vm22 <vivekmarwaha_at_gmail.com>
Date: Wed, 4 Feb 2009 06:52:04 -0800 (PST)
Message-ID: <fcb75268-516f-4a6f-9953-1cc1d36f00ec_at_35g2000pry.googlegroups.com>


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 10.2.0.2.
Is it possible to insert child records when the parent is being deleted? No.
Definition of Y_FK:

ALTER TABLE Y ADD (
  CONSTRAINT Y_FK
 FOREIGN KEY (ID)
 REFERENCES X (ID)); Regards,
Vivek

On Feb 3, 6:08 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Gerard H. Pille" <g..._at_skynet.be> wrote in messagenews:49887dab$0$2870$ba620e4c_at_news.skynet.be...
>
>
>
> >>>> 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Wed Feb 04 2009 - 08:52:04 CST

Original text of this message