注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

饥民2011

一直在搬砖

 
 
 

日志

 
 
 
 

oracle Deferrable constraint 详解以及用法.  

2013-10-12 18:56:20|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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张空表:

  1. create table T1 (id number(10,0),  
  2.                    nm varchar(10));  


接下来为id列 增加1个唯一约束t1_id:

  1. SQL> alter table T1 add constraint t1_id  unique(id);  
  2.   
  3. Table altered.  

注意这时并没有指定 Deferrable 属性, 所以默认情况下就是 not deferrable 的. 我们可以通过user_constraints视图来检查它的属性:


  1. SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';  
  2.   
  3. TABLE_NAME CONSTRAINT_NAME STATUS   DEFERRABLE     DEFERRED  VALIDATED  
  4. ---------- --------------- -------- -------------- --------- -------------  
  5. T1     T1_ID       ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED  

注意Deferrable 属性是 not deferrable.   而 Deferred 属性(Initially 初始属性) 是immediate.


这时我们连续执行3句insert sql:

  1. SQL> insert into T1 values(1, 'abc1');  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> insert into T1 values(2, 'abc2');  
  6.   
  7. 1 row created.  
  8.   
  9. SQL> insert into T1 values(2, 'abc3');  
  10. insert into T1 values(2, 'abc3')  
  11. *  
  12. ERROR at line 1:  
  13. 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了.

  1. SQL> rollback;  
  2.   
  3. Rollback complete.  


然后我们修改(删除再建立)那个约束, 射程deferrable initiallly immediate 模式.

  1. SQL> alter table T1 drop constraint t1_id;  
  2.   
  3. Table altered.  
  4.   
  5. SQL> alter table T1 add constraint t1_id unique(id) initially immediate deferrable ;  
  6.   
  7. Table altered.  
  8.   
  9. SQL>   


这时我们去user_constraints视图检查属性:

  1. SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';  
  2.   
  3. TABLE_NAME CONSTRAINT_NAME STATUS   DEFERRABLE     DEFERRED  VALIDATED  
  4. ---------- --------------- -------- -------------- --------- -------------  
  5. T1     T1_ID       ENABLED  DEFERRABLE     IMMEDIATE VALIDATED  

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



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

  1. SQL> insert into T1 values(1, 'abc1');  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> insert into T1 values(2, 'abc2');  
  6.   
  7. 1 row created.  
  8.   
  9. SQL> insert into T1 values(2, 'abc3');  
  10. insert into T1 values(2, 'abc3')  
  11. *  
  12. ERROR at line 1:  
  13. ORA-00001: unique constraint (BILL.T1_ID) violated  

可以到执行结果与上面一模一样. 没有区别. 也就是说也是case 1模式.


我们检索这张表, 只有两行数据, 然后回滚, 那么就清空这张表了.

  1. SQL> select * from T1;  
  2.   
  3.     ID NM  
  4. ---------- ----------  
  5.      1 abc1  
  6.      2 abc2  
  7.   
  8. SQL> rollback;  
  9.   
  10. Rollback complete.  
  11.   
  12. SQL> select * from T1;  
  13.   
  14. no rows selected  
  15.   
  16. SQL>   


好了, 现在就是重点了, deferrable可以在事务中临时改变成另1种模式.

也就是说可以由case1 临时设置为 case 2.

syntax:

set constraint  <constraint_name>  deferred/immediate


注意上面的语句并没有改变这个constraint的任何属性, 只不过是切换为另一种模式

也就是说初始是immediate模式的,   执行上面的语句后就临时变成deferred模式了.


我们接回上面的例子, 执行另一只模式:

  1. SQL> set constraint t1_id deferred;  
  2.   
  3. Constraint set.  

我们查下这个constraint的属性, 发现属性完全无改变,  Deferred属性还是immediate.  因为Deferred这个属性指的是初始属性.


  1. SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';  
  2.   
  3. TABLE_NAME CONSTRAINT_NAME STATUS   DEFERRABLE     DEFERRED  VALIDATED  
  4. ---------- --------------- -------- -------------- --------- -------------  
  5. T1     T1_ID       ENABLED  DEFERRABLE     IMMEDIATE VALIDATED  


这时我们insert上面的三条记录.  发现3条insert都可以被插入到数据表中.

  1. SQL> insert into T1 values(1, 'abc1');  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> insert into T1 values(2, 'abc2');  
  6.   
  7. 1 row created.  
  8.   
  9. SQL> insert into T1 values(2, 'abc3');  
  10.   
  11. 1 row created.  
  12.   
  13. SQL> select * from T1;  
  14.   
  15.     ID NM  
  16. ---------- ----------  
  17.      1 abc1  
  18.      2 abc2  
  19.      2 abc3  
  20.   
  21. SQL>   


则说明这时在insert 语句执行时不会执行这个约束的验证, 三行都被插入到表中了,  但是在提交时回验证.

  1. SQL> commit;  
  2. commit  
  3. *  
  4. ERROR at line 1:  
  5. ORA-02091: transaction rolled back  
  6. ORA-00001: unique constraint (BILL.T1_ID) violated  


见到提交失败, 而且整个事务会回滚,  也就是说这时表里面连第一第二句的insert记录也没有了.

  1. SQL> select * from T1;  
  2.   
  3. no rows selected  

这个跟not defferable constraint有区别啊,  那种在每一句sql statement执行验证, 而且验证失败时并不会回滚事务.



好了下面也是重点:

当我们commit失败后被回滚,  也就是代表当前的事务结束了. 

而上面我们提到过, 使用 set constraint 语句 切换constraint到另一种模式只会在当前事务中生效.

也就是指, 事务结束后, 这个constraint就会返回初始模式!


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

  1. SQL> insert into T1 values(1, 'abc1');  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> insert into T1 values(2, 'abc2');  
  6.   
  7. 1 row created.  
  8.   
  9. SQL> insert into T1 values(2, 'abc3');  
  10. insert into T1 values(2, 'abc3')  
  11. *  
  12. ERROR at line 1:  
  13. 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个事务中临时切换为 即时验证.

  评论这张
 
阅读(185)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017