Re: Initially deferred constraints

From: DeanB <deanbrown3d_at_yahoo.com>
Date: Mon, 8 Jun 2009 11:38:23 -0700 (PDT)
Message-ID: <09e30edd-3b07-4601-ba6c-79a3ea87c96d_at_o20g2000vbh.googlegroups.com>

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? Received on Mon Jun 08 2009 - 13:38:23 CDT