约束disable/enable validate/novalidate 的区别
转发http://www.cnblogs.com/rootq/archive/2008/09/23/1297400.html
disable/enable validate/novalidate 的区别
启用约束:
enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束.
enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.
禁用约束:
disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作.
disable validate :关闭约束,删除索引,不能对表进行 插入/更新/删除等操作.
环境:oracle 9i 9.0.1.0 for win,以上结论均测试通过.
例:disable validate约束后,执行update...操作提示:
ORA-25128: 不能对带有禁用和验证约束条件 (SYS.PK_EMP_01) 的表进行插入/更新/删除
=============================================================
今天看到一个关于enable novalidate的问题,以前书上看到过,但没仔细研究,趁此机会分析了一下,把结果发上来供大家参考.
首先对constraint的开启和关闭共有四种:enable validate;enable novalidate;disable validate;disable novalidate;
1,3没有什么好说的,2表示开启时不检查已存在数据,4表示不检查今后的数据.
为此做如下测试:
1.首先创建测试表
create table a (no number(5),name varchar2(10));
insert into a values(1,'ss');
alter table a add constraint pk_a primary key(no);
create table b (no number(5),name varchar2(10));
insert into a values(1,'ss');
ALTER TABLE b ADD CONSTRAINT fk_a_b
FOREIGN KEY (no)
REFERENCES a (no);
2.测试fk
SQL> insert into b values(2,'sd');
insert into b values(2,'sd')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.FK_A_B) violated - parent key not found
为此使用
SQL> alter table b disable novalidate constraint fk_a_b;
Table altered.
insert into b values(2,'sdd')
SQL> /
1 row created.
SQL> alter table b enable novalidate constraint fk_a_b;
Table altered.
从上面测试结果可见enable novalidate和disable novalidate在fk等约束是好用的.
3.测试pk
SQL> alter table a disable novalidate primary key;
Table altered.
SQL> insert into a values(1,'sd');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table a enable novalidate primary key;
alter table a enable novalidate primary key
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM.SYS_C001578) - primary key violated
在pk测试中enable novalidate不能使用了.
看到kamus的解释说是enable novalidate主键必须是唯一.
查了下相关资料,没有发现上述说明,但是找到一个下面的说法:
Primary and unique keys must use nonunique indexes
从新做测试
4.测试pk(2)
SQL> alter table a disable novalidate primary key;
Table altered.
这时我去检查表a,发现主键对应的索引pk_a不在了.
手工创建索引
SQL> create index i_a on a(no);
Index created.
SQL> alter table a enable novalidate primary key;
Table altered.
居然成功了.
结论:
从上面的测试结果看出,novalidate在非pk;un时可以正常工作.
在对pk;un使用时需要先创建相关索引,再使用novalidate.
2012.7.10 更新：语法novalidate对添加主键约束不起作用 (转)
最近在看tom的《Effective Oracle by design》，其中第6章6.3.8节有个例子是给表添加主键，但是不想对已经存在的数据进行校验：
alter table dept add constraint dept_pk primary key(deptno) rely enable novalidate;
我经过测试，发现即使加上novalidate语法也还是要检验的，测试如下：
SQL> create table aaa as select * from all_users;
Table created
SQL> insert into aaa select * from aaa;
29 rows inserted
SQL> alter table aaa add constraint pk_aaa primary key(username) rely enable novalidate;
alter table aaa add constraint pk_aaa primary key(username) rely enable novalidate
ORA-02437: 无法验证 (SMS.PK_AAA) - 违反主键
该测试在8.1.7.4、9.2.0.6、10.2.0.1中结果都一样，测试外键、check约束也都没有问题，就是主键不行！
最后发现问题所在：
创建主键时系统缺省还要同时创建一个唯一索引，novalidate只是使主键约束不对已经存在的数据
进行检验，并不对创建唯一索引进行控制，所以导致失败。可以在创建主键前先给该字段建个普通
非唯一索引，这样建主键时系统就会自动利用这个普通索引了。
SQL> create index ind_aaa on aaa(username);
Index created
SQL> alter table aaa add constraint pk_aaa primary key(username) rely enable novalidate;
Table altered
测试一下效果：
SQL> insert into aaa (username) values ('SYS');
insert into aaa (username) values ('SYS')
ORA-00001: 违反唯一约束条件 (SMS.PK_AAA)
更新：enable novalidate 和 validate的用处
http://zhaolinjnu.blog.sohu.com/35664664.html
SQL> create table t(id number);
Table created.
SQL> create index idx_t_id on t(id) online;
Index created.
ENABLE VALIDATE specifies that all old and new data also complies with the
constraint. An enabled validated constraint guarantees that all data is and will
continue to be valid.
If any row in the table violates the integrity constraint, the constraint remains
disabled and Oracle returns an error. If all rows comply with the constraint,
Oracle enables the constraint. Subsequently, if new data violates the constraint,
Oracle does not execute the statement and returns an error indicating the
integrity constraint violation.
SQL> select * from t;
ID
----------
1
2
2
SQL> commit;
Commit complete.
SQL> alter table t add constraint pk_t_id primary key(id)
2 enable validate;
alter table t add constraint pk_t_id primary key(id)
*
ERROR at line 1:
ORA-02437: cannot validate (TEST.PK_T_ID) - primary key violated
--enable validate会校验表中已有的数据是否违背此约束
ENABLE NOVALIDATE ensures that all new DML operations on the constrained
data comply with the constraint. This clause does not ensure that existing data
in the table complies with the constraint and therefore does not require a table
lock.
SQL> alter table t add constraint pk_t_id primary key(id)
2 enable novalidate;
Table altered.
这个过程因为不校验以前的数据是否违反此约束，只对新数据校验，所以在业务不繁忙的时候，阻塞DML查询的时间将会非常短暂。
SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_T_ID) violated
SQL> insert into t values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID
----------
1
2
2
3
--enable novalidate参数的含义是不检查表中已有的数据是否违反约束，但对新插入的数据会检查
If you specify neither VALIDATE nor NOVALIDATE, the default is VALIDATE.
If you change the state of any single constraint from ENABLE NOVALIDATE to
ENABLE VALIDATE, the operation can be performed in parallel, and does not block
reads, writes, or other DDL operations.
SQL> delete from t where id=2;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table t modify constraint pk_t_id validate;
Table altered.
通过上面的这种将一条创建主键的语句变成多条语句
1.create index idx_t_id on t(id) online compute statistics;
2.alter table t add constraint pk_t_id primary key(id)
enable novalidate;
补充：如果是加唯一约束，则用下面的语法：
alter table table_name add constraint uk_table_name unique(column_name...)
enable novalidate;
3.检查表中重复的主键的行
4.alter table t modify constraint pk_t_id validate;
备注：加enable参数的时候，才会阻塞DML语句
这种思路对生产环境有很重要的意义
