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估计在级联更新的情况下可以用到.
