oracle constraint的属性

yu6y7k72tr

　　
constraint有三个属性：
deferrable deferred validated；
其中 deferrable和deferred联合使用，可以控制何时判断数据完整 deferrable defered就可以在commit时再判断；
其中validated和enable联合使用，可以允许旧数据的不完整 enable not validated时，即可。
 
然后转一篇文章，讲解很详细：
转（http://space.itpub.net/17203031/viewspace-704016）
 
Oracle约束constraint是我们经常使用的一种数据库规则对象。constraint在数据库中的作用就是从静态角度对数据完整性进行维护。我们经常使用的主键primary key和外键foreign key，本质上就是约束的一种形式。
 
对Oracle的约束，我们有三个属性可以进行设置，分别为deferrable、deferred和validated。针对不同的需求设计场景，采用不同类型的属性，可以帮助我们实现不同的约束效果。下面我们分别来进行实验。
 
1、环境准备
 
首先我们还是准备数据实验环境。
 
 
SQL> create table t (id number);
Table created
 
SQL> alter table T
 2   add constraint c_t_id1
 3   check (id>5);
 
Table altered
 
 
我们创建了数据表T，在列id上添加了约束c_t_id1。约束内容很简单，就是要求id值保证是大于5。约束c_t_id1使用的是默认选项，数据字典中对该约束的表示如下：
 
 
SQL> select constraint_name, constraint_type ctype, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';
 
CONSTRAINT_NAME     CTYPE COND      STATUS  DEFERRABLE    DEFERRED VALIDATED
-------------------- ----- ---------- -------- -------------- --------- -------------
C_T_ID1             C    id>5      ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
 
 
注意，此时约束的三个属性取值分别为：deferrable: not deferrable；deferred：immediate；validated：validated；
 
我们观察一下此时数据表的插入现象：
 
 
SQL> insert into t values (1);//插入非法的数据；
insert into t values (1)
 
ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)//立刻报错，将数据剔除！
 
SQL> insert into t values (6);
1 row inserted
 
SQL> commit;
Commit complete
 
SQL> select * from t;
 
       ID
----------
        6
 
 
结论：在默认情况下，Oracle的约束是不允许延迟（not deferrable）、立即应用和验证的（immediate、validated）。在数据变化的时候，立即进行约束验证。
 
2、deferrable：约束应用可以延迟
 
deferrable默认值为not deferrable，字面含义是不可延迟。那么我们如果设置可以延迟，效果是什么呢？
 
 
SQL> alter table T
 2   drop constraint C_T_ID1;
 
Table altered
 
SQL> alter table T
 2   add constraint C_T_ID1
 3   check (id>5)
 4   deferrable;
 
Table altered
 
 
此时，数据字典中的情况是如下：
 
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';
 
CONSTRAINT_NAME     CTYPE STATUS  DEFERRABLE    DEFERRED VALIDATED
-------------------- ----- -------- -------------- --------- -------------
C_T_ID1             C     ENABLED DEFERRABLE    IMMEDIATE VALIDATED
 
 
与默认情况相比，deferrable属性变化为了deferrable。我们观察一下现象：
 
 
SQL> insert into t values (3);
insert into t values (3)
 
ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)
 
 
在插入数据的时候，立即进行约束验证。和默认情况下没有差异。那么怎么处理呢？
 
//手工设置deferred属性为deferred
SQL> set constraint c_t_id1deferred;
Constraints set
 
SQL> insert into t values (3);//此时插入数据时候，并不进行验证操作了。
1 row inserted
 
SQL> insert into t values (7);
1 row inserted
 
SQL> commit;
commit
 
ORA-02091: 事务处理已回退
ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)//直到进行commit的时候，才会应用约束；
 
 
那么，如何设置回原有的属性呢？
 
 
SQL> set constraint c_t_id1immediate;
Constraints set
 
SQL> insert into t values (4);//又恢复插入立刻检查约束的状态了？
insert into t values (4)
 
ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)
 
 
结论：单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后，约束检查延后是不可以直接使用的，要配合deferred参数，如果该参数是immediate，那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred，约束就是在事务commit提交的时候应用，出现错误就连带回滚rollback整个事务。
 
3、deferred：是否进行延迟应用
 
从上面的实验中，我们可以看出deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后，可以通过set constraints进行deferred属性的设置，来确定约束应用时点。
 
本部分确定deferred定义的方式和与deferrable属性的关系。是可以在定义约束是使用initially关键字来确定约束的deferred属性取值。
 
 
SQL> alter table T
 2   add constraint C_T_ID1
 3   check (id>5)
 4   deferrable initially deferred;
 
Table altered
 
 
set constraints语句只有在约束的deferrable属性设置为deferrable的时候才可以使用。
 
 
SQL> alter table T
 2   drop constraint C_T_ID1;
 
Table altered
 
SQL> alter table T
 2   add constraint C_T_ID1
 3   check (id>5)
 4 ;
 
Table altered
 
SQL> set constraint c_t_id1 deferred;
 
set constraint c_t_id1 deferred
 
ORA-02447: 无法延迟不可延迟的约束条件
 
 
4、disable禁用约束和validate验证约束
 
disable与validate的关系很紧密，相互制约影响。我们观察下面的实验：
 
 
SQL> alter table t disable constraint c_t_id1;
 
Table altered
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';
 
CONSTRAINT_NAME COND      STATUS  DEFERRABLE    DEFERRED VALIDATED
---------------- ---------- -------- -------------- --------- -------------
C_T_ID1         id>5      DISABLEDNOT DEFERRABLE IMMEDIATENOT VALIDATED
 
 
通过disable constraint语句，可以对一个约束进行禁用操作。禁用disable下的约束，validated属性是not validate，也就不起作用的。
 
 
SQL> select * from t;
 
       ID
----------
        6
        2
        4
       86
 
 
数据表约束禁用后，数据完整性被破坏。此时，如果我们直接进行约束启用。
 
 
SQL> alter table t enable constraint c_t_id1;
 
alter table t enable constraint c_t_id1
 
ORA-02293: 无法验证 (SYS.C_T_ID1) - 违反检查约束条件
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';
 
CONSTRAINT_NAME COND      STATUS  DEFERRABLE    DEFERRED VALIDATED
---------------- ---------- -------- -------------- --------- -------------
C_T_ID1         id>5      DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED
 
 
在约束被启用的时候，会自动进行检验。如果数据不满足条件，Oracle不会开启该约束引用。
 
enable和validate可以配合使用enable novalidate子句使用。
 
 
SQL> alter table t enable novalidate constraint c_t_id1;
Table altered
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';
 
CONSTRAINT_NAME COND      STATUS  DEFERRABLE    DEFERRED VALIDATED
---------------- ---------- -------- -------------- --------- -------------
C_T_ID1         id>5      ENABLED NOT DEFERRABLE IMMEDIATENOT VALIDATED
 
 
此时，只是针对现有T中数据不进行验证，对新增加变化的数据，同样会进行验证。
 

SQL> insert into t values (45);
1 row inserted
 
SQL> commit;
Commit complete
 
SQL> insert into t values (3);
insert into t values (3)
 
ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)
 
 
 
5、结论
 
默认情况下，Oracle constraint是不开启延迟约束和原有数据保留验证的。那么在什么样的场景下，我们可以考虑使用这些特性呢？
 
ü       批量数据导入、物化视图刷新的时候，事务量很大，而且存在数据规律前后颠倒的情况。此时，如果开启着立即约束应用的开关，可能存在一些暂时性的约束不满足的情况，从而影响到整个系统的运行。开启约束延迟验证，就可以帮助我们解决这个问题；
ü       历史数据移植。历史数据存在不规则的情况，很多时候难以满足我们新系统的数据完整性要求。可以使用not validate的方式，对历史数据不进行约束控制，而只针对新数据开启管理；