一、Oracle约束的状态
Oracle完整性约束的状态有4种,分别是ENABLE、DISABLE、VALIDATE、NOVALIDATE。
ENABLE 表示Oracle将检查要插入或更新的数据库中的数据是否符合约束;
DISABLE 表示表中可以存放违反约束的行;
VALIDATE 表示数据库验证表中的已存在数据是否符合约束;
NOVALIDATE 表示数据库不验证表中已存在数据是否符合约束。
Oracle默认约束状态为ENABLE、VALIDATE。
下面看Oracle官方给出的汇总:
Modified Data | Existing Data | Summary |
---|---|---|
|
|
Existing and future data must obey the constraint. An attempt to apply a new constraint to a populated table results in an error if existing rows violate the constraint. |
|
The database checks the constraint,but it need not be true for all rows. Thus,existing rows can violate the constraint,but new or modified rows must conform to the rules. |
|
|
|
The database disables the constraint,drops its index,and prevents modification of the constrained columns. |
|
The constraint is not checked and is not necessarily true. |
下面使用实例测试各状态:
创建测试表
- zx@ORA11G>createtablet1(idnumber,namevarchar2(10),addressvarchar2(10));
- Tablecreated.
- zx@ORA11G>insertintot1values(1,'zx','hb');
- 1rowcreated.
- zx@ORA11G>insertintot1values(1,'zq','jx');
- 1rowcreated.
- zx@ORA11G>insertintot1values(2,'wl','sd');
- 1rowcreated.
- zx@ORA11G>commit;
- Commitcomplete.
1、测试ENABLE、VALIDATE状态
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id);
- altertablet1addconstraintt1_ukunique(id)
- *
- ERRORatline1:
- ORA-02299:cannotvalidate(ZX.T1_UK)-duplicatekeysfound
因为id列中有重复值,此时创建约束t1_uk的状态为ENABLE、VALIDATE会验证表中已存在的数据,所以创建约束不成功。删除表中的重复数据再次创建约束即可成功。
- zx@ORA11G>deletefromt1whereid=1andname='zq';
- 1rowdeleted.
- zx@ORA11G>commit;
- Commitcomplete.
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id);
- Tablealtered.
- zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';
- TABLE_NAME CONSTRAINT_NAME CDEFERRABLESTATUS VALIDATED
- ------------------------------------------------------------------------------------------------
- T1 T1_UK UNOTDEFERRABLEENABLED VALIDATED
创建完成后再次插入id=1的数据即会报错,说明约束状态为ENABLE
- zx@ORA11G>insertintot1values(1,'jx');
- insertintot1values(1,'jx')
- *
- ERRORatline1:
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
2、测试ENABLE、DISABLED状态
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
- 1zq jx
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)enablenovalidate;
- altertablet1addconstraintt1_ukunique(id)enablenovalidate
- *
- ERRORatline1:
- ORA-02299:cannotvalidate(ZX.T1_UK)-duplicatekeysfound
直接创建unique约束报错,因为有重复值。但先在id列上创建索引,然后创建unique约束即可成功。
- zx@ORA11G>createindexidx_t_idont1(id);
- Indexcreated.
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)usingindexidx_t_idenablenovalidate;
- Tablealtered.
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
- 1zq jx
- zx@ORA11G>selecttable_name,validatedfromuser_constraintswheretable_name='T1';
- TABLE_NAME CONSTRAINT_NAME CDEFERRABLESTATUS VALIDATED
- ------------------------------------------------------------------------------------------------
- T1 T1_UK UNOTDEFERRABLEENABLED NOTVALIDATED
原表中的id列中有重复值,还是可以创建unique约束,因为状态指定为NOVALIDATE,不验证表中已有的数据。另外因为状态为ENABLE,再次插入重复值报错:
- zx@ORA11G>insertintot1values(2,'yc','bj');
- insertintot1values(2,'bj')
- *
- ERRORatline1:
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
3、测试DISABLE、VALIDATE状态
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)usingindexidx_t_iddisablevalidate;
- Tablealtered.
- zx@ORA11G>selecttable_name,validatedfromuser_constraintswheretable_name='T1';
- TABLE_NAME CONSTRAINT_NAME CDEFERRABLESTATUS VALIDATED
- ------------------------------------------------------------------------------------------------
- T1 T1_UK UNOTDEFERRABLEDISABLEDVALIDATED
- zx@ORA11G>insertintot1values(1,'jx')
- *
- ERRORatline1:
- ORA-25128:Noinsert/update/deleteontablewithconstraint(ZX.T1_UK)disabledandvalidated
DISABLE、VALIDATE状态下,不允许做增删改操作。
4、测试DISABLE、NOVALIDATE状态
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
- 1zq jx
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)usingindexidx_t_iddisablenovalidate;
- Tablealtered.
- zx@ORA11G>selecttable_name,validatedfromuser_constraintswheretable_name='T1';
- TABLE_NAME CONSTRAINT_NAME CDEFERRABLESTATUS VALIDATED
- ------------------------------------------------------------------------------------------------
- T1 T1_UK UNOTDEFERRABLEDISABLEDNOTVALIDATED
- zx@ORA11G>insertintot1values(2,'bj');
- 1rowcreated.
- zx@ORA11G>commit;
- Commitcomplete.
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
- 1zq jx
- 2yc bj
约束状态为DISABLE、NOVALIDATE,对新数据和老数据都不做验证。
二、验证机制
1. 两种验证时机.
Oracle的constraints(约束) 根据验证时机可以分成两种.
case 1. 在每一句insert statement 执行时就会马上验证,如果约束验证失败,则这句sql statement 会执行失败.
case 2. 执行insert statements 时不会验证,在commit的时候验证,如果验证失败,则整个Transaction 回滚.
2.constraints的分类
对应地,oracle的 constraints 也可以分成两大类.
一种是not deferrable (不可以延时的) . 这种情况下只能执行 case1 的验证时机(即时验证)
另一种是 deferrable (可以设置成延时的). 这种情况下可以执行 case 1 或 case2 的验证时机. 但需要设置.
对于第二种defferable 分类,还可以分成两小类.
一种是 initially immediate,意思时默认情况下执行case 1.
另一种是initially deferred,意思是默认情况下执行case2.
也就是可以分成三种,如下图:
2.1、not deferrable
这种最常见也最简单. 如果在增加1个constraint 时不指定验证时机属性. 默认情况下就会被设为not deferrable.既然constraint 是不可以延时验证的,所以也不用设定它的初始属性(实际上就是initially immediate)。
清空上面的t1表,并创建一个unique约束
- zx@ORA11G>truncatetablet1;
- Tabletruncated.
- zx@ORA11G>select*fromt1;
- norowsselected
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)notdeferrable;
- Tablealtered.
- zx@ORA11G>selecttable_name,validatedfromuser_constraintswheretable_name='T1';
- TABLE_NAME CONSTRAINT_NAME CDEFERRABLESTATUS VALIDATED
- ------------------------------------------------------------------------------------------------
- T1 T1_UK UNOTDEFERRABLEENABLED VALIDATED
约束为NOT DEFERRABLE状态,插入测试数据查看状态:
- zx@ORA11G>insertintot1values(1,'hb');
- 1rowcreated.
- zx@ORA11G>insertintot1values(2,'sd');
- 1rowcreated.
- zx@ORA11G>insertintot1values(1,'jx')
- *
- ERRORatline1:
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
插入第三条数据时因为有重复数据,直接报错,说明验证时机为case1:即时验证,但不会回滚之前插入的结果。
2.2、 deferrable、initially immediate状态
- zx@ORA11G>altertablet1dropconstraintt1_uk;
- Tablealtered.
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)deferrableinitiallyimmediate;
- Tablealtered.
- zx@ORA11G>selecttable_name,validatedfromuser_constraintswheretable_name='T1';
- TABLE_NAME CONSTRAINT_NAME CDEFERRABLESTATUS VALIDATED
- ------------------------------------------------------------------------------------------------
- T1 T1_UK UDEFERRABLEENABLED VALIDATED
- zx@ORA11G>insertintot1values(1,'jx')
- *
- ERRORatline1:
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
- zx@ORA11G>select*fromt1;
- IDNAMEADDRESS
- ------------------------------
- 1zx hb
- 2wl sd
插入第三条数据时报错因为有重复值,说明验证时机为case1:即时验证,这与前一种状态一样。那为什么还要设置这样一种状态呢?我们来执行下面的语句:
- zx@ORA11G>setconstraintt1_ukdeferred;
- Constraintset.
上面的语句并没有改变这个constraint的任何属性,只不过是切换为另一种模式
也就是说初始是immediate模式的,执行上面的语句后就临时变成deferred模式了.
再次执行前面的插入语句:
- zx@ORA11G>insertintot1values(1,'jx');
- 1rowcreated.
第三条也能插入进去,下面尝试commit:
- zx@ORA11G>commit;
- commit
- *
- ERRORatline1:
- ORA-02091:transactionrolledback
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
- zx@ORA11G>select*fromt1;
- norowsselected
commit时报错,查询t1表,没有任何数据,说明回滚了整个事务。即case2:延迟验证。此时再次执行上面的三次插入操作:
- zx@ORA11G>insertintot1values(1,'jx')
- *
- ERRORatline1:
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
从上面结果可以看出,插入第三行时又报错,说明上面的set constraint语句的作用范围只有当前的一个事务。事务结束后即约束状态即回到原模式。
2.3、deferrable、initially deferred
有了上面的第二个实验就可以很容易的理解这一状态了。
- zx@ORA11G>altertablet1dropconstraintt1_uk;
- Tablealtered.
- zx@ORA11G>altertablet1addconstraintt1_ukunique(id)deferrableinitiallydeferred;
- Tablealtered.
- zx@ORA11G>selecttable_name,'jx');
- 1rowcreated.
- zx@ORA11G>commit;
- commit
- *
- ERRORatline1:
- ORA-02091:transactionrolledback
- ORA-00001:uniqueconstraint(ZX.T1_UK)violated
- zx@ORA11G>select*fromt1;
- norowsselected
参考:http://blog.csdn.net/nvd11/article/details/12654691
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT33337