disable/enable validate/novalidate 的区别 (ORACLE)

启用约束:
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) 的表进行插入/更新/删除

=============================================================

首先对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.

Validated Constraints
You have seen how to enable and disable a constraint. ENABLE and DISABLE
affect only future data that will be added/modified in the table. In contrast,
the VALIDATE and NOVALIDATE keywords in the ALTER TABLE command act
upon the existing data. Therefore, a constraint can have four states:

ENABLE VALIDATE This is the default for the ENABLE clause. The existing
data in the table is validated to verify that it conforms to the constraint.

ENABLE NOVALIDATE Does not validate the existing data, but enables
the constraint for future constraint checking.

DISABLE VALIDATE The constraint is disabled (any index used to
enforce the constraint is also dropped), but the constraint is kept valid.
No DML operation is allowed on the table because future changes cannot
be verified.

DISABLE NOVALIDATE This is the default for the DISABLE clause. The
constraint is disabled, and no checks are done on future or existing data.

eg:
ALTER TABLE WH01 MODIFY CONSTRAINT PK_WH01
DISABLE NOVALIDATE;