约束延迟

约束延迟：

批量插入，更新时不必设置延迟。

但如果是分开成几个SQL做，则需要设置。

CREATE TABLE t3 (a int, b int );

 

  insert into t3values(2,1);

  insert into t3values(1,4);

  insert into t3values(4,5);

  insert into t3values(5,2);

 

CREATE TABLE t2 (a int, b int unique, CONSTRAINT t2_aFOREIGN KEY (a) REFERENCES t2(b) DEFERRABLE INITIALLY IMMEDIATE );

  insert into t2values(2,1);

  insert into t2values(1,2);

 

insert into t2 select * from t3;

update t2 set a=a+5,b=b+5;

 

set constraint t2_a deferred; 

insert into t2 values(2,1);

  insert into t2values(1,2);

 

  tester@AD_TEST>CREATE TABLE t2 (a int, b int unique, CONSTRAINT t2_a FOREIGN KEY (a)REFERENCES t2(b) DEFERRABLE INITIALLY IMMEDIATE );

 

Table created.

 

tester@AD_TEST> insert into t2 values(2,1);

insert into t2 values(2,1)

*

ERROR at line 1:

ORA-02291: integrity constraint (TESTER.T2_A) violated -parent key not found

 

 

tester@AD_TEST> insert into t2 as select * from t3;

insert into t2 as select * from t3

               *

ERROR at line 1:

ORA-00926: missing VALUES keyword

 

 

tester@AD_TEST> insert into t2 select * from t3;

 

4 rows created.

tester@AD_TEST>    update t2 set a=a+5,b=b+5;

 

4 rows updated.

tester@AD_TEST> set constraint t2_a deferred;

insert into t2 values(2,1);

  insert into t2values(1,2);

 

Constraint set.

 

tester@AD_TEST>

1 row created.

 

tester@AD_TEST>

1 row created.