Re: Initially deferred constraints

From: ddf <oratune_at_msn.com>
Date: Mon, 8 Jun 2009 13:20:41 -0700 (PDT)
Message-ID: <f1fefb52-5d4d-4db3-947b-3ad7a1dd389e_at_p4g2000vba.googlegroups.com>

On Jun 8, 2:42 pm, DeanB <deanbrow..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

My apologies for not fully understanding your initial question. I'm happy you found the answer.

David Fitzjarrell Received on Mon Jun 08 2009 - 15:20:41 CDT