oracle Deferrable constraint 详解以及用法.

客官°小女子只卖身不卖艺 2022-09-17 10:28 184阅读 0赞

## 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.

如下图:

![444127020131012180125015.gif][]

下面会举一些例子来详细解释这个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视图检查属性:

可以见到只有1个属性与Not Defferable constraint有区别,  就是 deferrable  属性是 deferrable了.   但是 deferred (初始属性) 还是 immediate.

我们继续尝试插入上面的三行数据:

可以到执行结果与上面一模一样. 没有区别. 也就是说也是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这个属性指的是初始属性.

这时我们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就会返回初始模式!**

我们再尝试插入三条语句:

发现再次在第三句执行时被执行即时验证, 因为在新事务中,  constraint t1\_id又切换回 immediate 模式了.

### 2.3 deferrable initially deferred constraints ###