Re: Initially deferred constraints

From: DeanB <deanbrown3d_at_yahoo.com>
Date: Mon, 8 Jun 2009 12:42:30 -0700 (PDT)
Message-ID: <45e73a1b-4f26-4f48-830e-a6c0de28d739_at_l28g2000vba.googlegroups.com>

On Jun 8, 2:38 pm, DeanB <deanbrow..._at_yahoo.com> wrote:
> On Jun 8, 12:35 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Jun 8, 10:16 am, dean <deanbrow..._at_yahoo.com> wrote:
>
> > > Can anyone explain to me when a constraint can be deferred, but not
> > > initially deferred? My understanding is that initially deferred does
> > > not do its check until the close of a transaction, but I am not sure
> > > what NOT initially deferred means.
>
> > > Thanks!
>
> > SQL> --
> > SQL> -- Create test tables
> > SQL> --
> > SQL> create table pk_tbl(
> >   2          mypk number,
> >   3          myfk number,
> >   4          myval varchar2(40)
> >   5  );
>
> > Table created.
>
> > SQL>
> > SQL> create table fk_tbl(
> >   2          mypk number,
> >   3          myval varchar2(40)
> >   4  );
>
> > Table created.
>
> > SQL>
> > SQL> --
> > SQL> -- Add primary keys
> > SQL> --
> > SQL> alter table pk_tbl
> >   2  add constraint pk_tbl_pk
> >   3  primary key(mypk);
>
> > Table altered.
>
> > SQL>
> > SQL> alter table fk_tbl
> >   2  add constraint fk_tbl_pk
> >   3  primary key(mypk);
>
> > Table altered.
>
> > SQL>
> > SQL> --
> > SQL> -- Add foreign key
> > SQL> --
> > SQL> -- by default this constraint
> > SQL> -- is not initially deferred
> > SQL> --
> > SQL> alter table pk_tbl
> >   2  add constraint pk_tbl_fk
> >   3  foreign key(myfk)
> >   4  references fk_tbl;
>
> > Table altered.
>
> > SQL>
> > SQL> --
> > SQL> -- Try to insert data into pk_tbl
> > SQL> --
> > SQL> -- Current foreign key constraint prevents this
> > SQL> --
> > SQL> insert into pk_tbl
> >   2  values(1,1,'Test 1');
> > insert into pk_tbl
> > *
> > ERROR at line 1:
> > ORA-02291: integrity constraint (BING.PK_TBL_FK) violated - parent key
> > not
> > found
>
> > SQL>
> > SQL> --
> > SQL> -- Inseert data in the correct order
> > SQL> -- and all is well
> > SQL> --
> > SQL> insert into fk_tbl
> >   2  values(1, 'Rest of test 1');
>
> > 1 row created.
>
> > SQL>
> > SQL> insert into pk_tbl
> >   2  values(1,1,'Test 1');
>
> > 1 row created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> --
> > SQL> -- Drop not deferred foreign key
> > SQL> --
> > SQL> alter table pk_tbl
> >   2  drop constraint pk_tbl_fk;
>
> > Table altered.
>
> > SQL>
> > SQL> --
> > SQL> -- Truncate tables
> > SQL> --
> > SQL> truncate table fk_tbl;
>
> > Table truncated.
>
> > SQL> truncate table pk_tbl;
>
> > Table truncated.
>
> > SQL>
> > SQL> --
> > SQL> -- Add foreign key as initially deferred
> > SQL> --
> > SQL> alter table pk_tbl
> >   2  add constraint pk_tbl_fk
> >   3  foreign key(myfk)
> >   4  references fk_tbl
> >   5  initially deferred;
>
> > Table altered.
>
> > SQL>
> > SQL> --
> > SQL> -- Insert data in 'backwards' order
> > SQL> --
> > SQL> -- Deferred FK constraint allows parent to
> > SQL> -- be populated first
> > SQL> --
> > SQL> insert into pk_tbl
> >   2  values(1,1,'Test 1');
>
> > 1 row created.
>
> > SQL>
> > SQL> insert into fk_tbl
> >   2  values(1, 'Rest of test 1');
>
> > 1 row created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
>
> > David Fitzjarrell
>
> David - I understand fully your example - but there are two columns on
> the Enterprise Manager that are related - Deferrable, and Initially
> Deferred. What I'm asking is this: what's the difference between the
> two?- Hide quoted text -
>
> - Show quoted text -

Never mind I think I got the answer.
FWIW: This statement:

set constraints all deferred;

alters all constraints that are deferrable (whether they are immediate or not) to be deferred until the transaction is committed. However, if a check is NOT defined as deferrable, then that check is not deferred with the statement above, and the check can never temporarily be allowed to fail.

The set constraints statement is reset on either commit or rollback of the transaction. Received on Mon Jun 08 2009 - 14:42:30 CDT