1. 两种验证时机.
Oracle的constraints(约束) 根据验证时机可以分成两种.
case 1. 在每一句insert statement 执行时就会马上验证, 如果约束验证失败, 则这句sql statement 会执行失败.
case 2. 执行insert statements 时不会验证, 在commit的时候验证, 如果验证失败, 则整个Transaction 回滚.
2.constraints的分类
对应地, oracle 的 constraints 也可以分成两大类.
一种是not deferrable (不可以延时的) . 这种情况下只能执行 case1 的验证时机(即时验证)
另一种是 defferable (可以设置成延时的). 这种情况下可以执行 case 1 或 case2 的验证时机. 但需要设置.
对于第二种defferable 分类, 还可以分成两小类.
一种是 initially immediate , 意思时默认情况下执行case 1.
另一种是initially deferred, 意思是默认情况下执行case2.
也就是可以分成三种
1. not deferrable
2. deferrable initially immediate
3. deferrable initially deferred.
如下图:
下面会举一些例子来详细解释这个3种 constraints的区别:
2.1 not deferrable constraints
这种最常见也最简单. 如果在增加1个constraint 时不指定验证时机属性. 默认情况下就会被设为not deferrable.
既然constraint 是不可以延时验证的, 所以也不用设定它的初始属性(实际上就是initially immediate)
例子:
首先我们建立1张空表:
create table T1 (id number(10,0),
nm varchar(10));
接下来为id列 增加1个唯一约束t1_id:
SQL> alter table T1 add constraint t1_id unique(id);
Table altered.
注意这时并没有指定 Deferrable 属性, 所以默认情况下就是 not deferrable 的. 我们可以通过user_constraints视图来检查它的属性:
SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';
TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1 T1_ID ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
注意Deferrable 属性是 not deferrable. 而 Deferred 属性(Initially 初始属性) 是immediate.
这时我们连续执行3句insert sql:
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated
可以见到第一第二句insert statement 能正常执行, 但第三句执行失败, 因为id=2 与第二句重复了, 约束验证失败.
所以说验证时机属于case1, 即时验证.
这种验证时机也是最常见的.
2.2 deferrable initially immediate constraints
这种约束默认情况下等于第一种约束(not deferrable), 但是它可以在事务中改变设置, 变成延时验证的约束.
我们可以这样理解.
第一个属性deferrable 表示 这个约束可以被设置成延时验证.
第二个属性initially immediate 表示这个约束默认情况下是即时验证.
我们继续利用上面的例子.
首先, 清空上面那个例子表. 由于上面操作没有提交, 所以回滚就ok了.
SQL> rollback;
Rollback complete.
然后我们修改(删除再建立)那个约束, 射程deferrable initiallly immediate 模式.
SQL> alter table T1 drop constraint t1_id;
Table altered.
SQL> alter table T1 add constraint t1_id unique(id) initially immediate deferrable ;
Table altered.
SQL>
这时我们去user_constraints视图检查属性:
SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';
TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1 T1_ID ENABLED DEFERRABLE IMMEDIATE VALIDATED
可以见到只有1个属性与Not Defferable constraint有区别, 就是 deferrable 属性是 deferrable了. 但是 deferred (初始属性) 还是 immediate.
我们继续尝试插入上面的三行数据:
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated
可以到执行结果与上面一模一样. 没有区别. 也就是说也是case 1模式.
我们检索这张表, 只有两行数据, 然后回滚, 那么就清空这张表了.
SQL> select * from T1;
ID NM
---------- ----------
1 abc1
2 abc2
SQL> rollback;
Rollback complete.
SQL> select * from T1;
no rows selected
SQL>
好了, 现在就是重点了, deferrable可以在事务中临时改变成另1种模式.
也就是说可以由case1 临时设置为 case 2.
syntax:
set constraint <constraint_name> deferred/immediate
注意上面的语句并没有改变这个constraint的任何属性, 只不过是切换为另一种模式
也就是说初始是immediate模式的, 执行上面的语句后就临时变成deferred模式了.
我们接回上面的例子, 执行另一只模式:
SQL> set constraint t1_id deferred;
Constraint set.
我们查下这个constraint的属性, 发现属性完全无改变, Deferred属性还是immediate. 因为Deferred这个属性指的是初始属性.
SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';
TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1 T1_ID ENABLED DEFERRABLE IMMEDIATE VALIDATED
这时我们insert上面的三条记录. 发现3条insert都可以被插入到数据表中.
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
1 row created.
SQL> select * from T1;
ID NM
---------- ----------
1 abc1
2 abc2
2 abc3
SQL>
则说明这时在insert 语句执行时不会执行这个约束的验证, 三行都被插入到表中了, 但是在提交时回验证.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (BILL.T1_ID) violated
见到提交失败, 而且整个事务会回滚, 也就是说这时表里面连第一第二句的insert记录也没有了.
SQL> select * from T1;
no rows selected
这个跟not defferable constraint有区别啊, 那种在每一句sql statement执行验证, 而且验证失败时并不会回滚事务.
好了下面也是重点:
当我们commit失败后被回滚, 也就是代表当前的事务结束了.
而上面我们提到过, 使用 set constraint 语句 切换constraint到另一种模式只会在当前事务中生效.
也就是指, 事务结束后, 这个constraint就会返回初始模式!
我们再尝试插入三条语句:
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated
发现再次在第三句执行时被执行即时验证, 因为在新事务中, constraint t1_id又切换回 immediate 模式了.
2.3 deferrable initially deferred constraints
如果理解了第二种constraint, 那么最后一种就很简单了
第一个属性deferrable 表示 这个约束可以被设置成延时验证.
第二个属性initially immediate 表示这个约束默认情况下是延时验证.
但是可以用set constraint语句 在1个事务中临时切换为 即时验证.
![oracle中的start with connect by用法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)