Re: Initially deferred constraints
From: ddf <oratune_at_msn.com>
Date: Mon, 8 Jun 2009 09:35:47 -0700 (PDT)
Message-ID: <f4bd9735-e57e-46dd-af7b-06633ec9c4bd_at_f10g2000vbf.googlegroups.com>
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!
5 );
4 );
2 add constraint pk_tbl_pk
3 primary key(mypk);
2 add constraint pk_tbl_fk
3 foreign key(myfk)
4 references fk_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
2 values(1, 'Rest of test 1');
2 drop constraint pk_tbl_fk;
2 add constraint pk_tbl_fk
3 foreign key(myfk)
4 references fk_tbl
5 initially deferred;
2 values(1,1,'Test 1');
Date: Mon, 8 Jun 2009 09:35:47 -0700 (PDT)
Message-ID: <f4bd9735-e57e-46dd-af7b-06633ec9c4bd_at_f10g2000vbf.googlegroups.com>
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 Received on Mon Jun 08 2009 - 11:35:47 CDT