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) deferrable[C1] ;

 

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     IMMEDIATE[C2] 

PK_ID_AA             AA                   DEFERRABLE     IMMEDIATE

 

SQL> update[C3]  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> select * from aa[C4] ;

 

        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> select * from aa[C5] ;

 

        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 

---

 [C1]此处必须指定deferrable，否则后续set constraint语句无效

 [C2]Initial clause不指定的情况下是默认值是immediate

 [C3]虽然fk_id_aa1已经打开deferrable开关，但在不指定initial clause的情况下是initial immediate，所以还是会有违反constraint的报错

 [C4]Commit时一旦出现违反constraint的情况，会立即回滚之前的更改

 [C5]set constraint fk_id_aa1 immediate后同样检测到违反constraint的情况，但不会回滚之前的操作这点与commit不同

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1312402/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1312402/