Oracle deferrable、immediate 约束

oracle数据库中的所有约束默认情况下都是立即触发的,也就是说在语句运行结束后数据库会检查数据的一致性状态.

其中not deferrable 与initially immediate是一样的,与deferrable initially immediate则不同。后者允许将constraint再改为initially deferred, 前者不允许更改:
>create table test2 (f1 number(3) constraint fk references test(f1) initially immediate);
>alter table test2 modify constraint fk initially deferred;
alter table test2 modify constraint fk initially deferred
*
ERROR 位于第 1 行:
ORA-02447: 无法延迟不可延迟的约束条件

> create table test2 (f1 number(3) constraint fk references test(f1) deferrable initially immediate);
> alter table test2 modify constraint fk initially deferred;
表已更改。 [oracle@neworacle oracle]$ sqlplus mayp/maypSQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 26 11:05:50 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning and Data Mining optionsSQL> create table p(x int);Table created.SQL> alter table p add constraint pk_p primary key(x) ;Table altered. SQL> insert into p values(1);1 row created.SQL> insert into p values(1);
insert into p values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (MAYP.PK_P) violated

这里在没有commit前 oracle就已经检测到了主键重复.如果是deferrable约束呢?

SQL> set constraint pk_p deferred;
set constraint pk_p deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

注意这里不能直接修改约束的状态,必须重新创建deferrable的约束后,才能修改。

SQL> alter table p drop constraint pk_p;

Table altered.

SQL> alter table p add constraint pk_p primary key(x) deferrable;

Table altered.

现在的约束为deferrable状态:

SQL> select x from p;   

         X
———-
         1

SQL> set constraint pk_p deferred;

Constraint set.

SQL> insert into p values(1);

1 row created.

这里oracle还没有验证这个插入的1是重复的.提交的时候才验证.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (MAYP.PK_P) violated

如果修改约束为immediate呢?

SQL> set constraint pk_p immediate;

Constraint set.

SQL> insert into p values(1);
insert into p values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (MAYP.PK_P) violated

约束状态回到了最初的情况.

deferrable估计在级联更新的情况下可以用到.