ITPub博客

首页 > 数据库 > Oracle > Constraint deferrable特性研究

Constraint deferrable特性研究

原创 Oracle 作者:oliseh 时间:2014-10-28 22:24:45 0 删除 编辑

开启一个constraintDeferrable特性后,可以在一个transaction之内对于违反constraint的操作不报错。Constraintdeferrable特性在建立constraint是指定,并且之后不能更改,要更改只有重建constraint。创建constraint时的INITIALLY Clause可以指定constraint是否具有deferrable属性,若此处不指定,也可以使用set constraints … deferredtransaction中指定。下面是几个常见的场景:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616867