Constraint deferrable特性研究

開啟一個constraint的Deferrable特性後，可以在一個transaction之內對於違反constraint的操作不報錯。Constraint的deferrable特性在建立constraint是指定，並且之後不能更改，要更改只有重建constraint。建立constraint時的INITIALLY Clause可以指定constraint是否具有deferrable屬性，若此處不指定，也可以使用set constraints … deferred在transaction中指定。下面是幾個常見的場景：

1、 使用set constraint … deferred在一個transaction避免update主鍵時報錯

SQL> create table aa (id number) tablespace users;

 

Table created.

 

SQL> insert into aa values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> create table aa1 (id number,name varchar2(2)) tablespace users;

 

Table created.

 

SQL> insert into aa1 values(1,'a');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter table aa add constraint pk_id_aa primary key(id) deferrable using index tablespace users;

 

Table altered.

 

SQL> alter table aa1 add constraint fk_id_aa1 foreign key(id) references aa(id)  ;

 

Table altered.

 

SQL> select constraint_name,table_name,DEFERRABLE,DEFERRED from dba_constraints where constraint_name in ('PK_ID_AA','FK_ID_AA1');

 

CONSTRAINT_NAME      TABLE_NAME           DEFERRABLE     DEFERRED

-------------------- -------------------- -------------- ---------

FK_ID_AA1            AA1                  DEFERRABLE      

PK_ID_AA             AA                   DEFERRABLE     IMMEDIATE

 

SQL>   aa set id=2 where id=1;

update aa set id=2 where id=1

*

ERROR at line 1:

ORA-02292: integrity constraint (SYS.FK_ID_AA1) violated - child record found

 

SQL> set constraint fk_id_aa1 deferred;

 

Constraint set.

 

SQL> update aa set id=2 where id=1;

 

1 row updated.

 

SQL> update aa1 set id=2 where id=1;

 

1 row updated.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from aa;

 

        ID

----------

         2

 

SQL> select * from aa1;

 

        ID NA

---------- --

         2 a

 

 

2、 使用set constraint… immediate測試之前的操作是否違反constraint，避免使用commit後一旦檢測到有違反constraint的情況會造成所有更改回滾的情況

SQL> select * from aa;

 

        ID

----------

         2

 

SQL> select * from aa1;

 

        ID NA

---------- --

         2 a

 

SQL> set constraint fk_id_aa1 deferred;

 

Constraint set.

 

SQL> update aa set id=3 where id=2;

 

1 row updated.

 

SQL> select * from aa;

 

        ID

----------

         3

 

SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02292: integrity constraint (SYS.FK_ID_AA1) violated - child record found

 

 

SQL>  ;

 

        ID

----------

         2

 

SQL> select * from aa;

 

        ID

----------

         2

 

SQL> select * from aa1;

 

        ID NA

---------- --

         2 a

 

SQL> set constraint fk_id_aa1 deferred;

 

Constraint set.

 

SQL> update aa set id=3 where id=2;

 

1 row updated.

 

SQL> select * from aa;

 

        ID

----------

         3

 

SQL> set constraint fk_id_aa1 immediate;

set constraint fk_id_aa1 immediate

*

ERROR at line 1:

ORA-02291: integrity constraint (SYS.FK_ID_AA1) violated - parent key not found

 

 

SQL>  ;

 

        ID

----------

         3

 

SQL> update aa1 set id=3 where id=2;

 

1 row updated.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from aa;

 

        ID

----------

         3

 

SQL> select * from aa1;

 

        ID NA

---------- --

         3 a