enable/disable novalidate的一点总结

我们知道对constraint的开启和关闭共有四种:enable validate;enable novalidate;disable validate;disable novalidate。

enable validate/disable validate 这两种没什么说的。
enable novalidate/disable novalidate 分别表示开启时不检查已存在数据/不检查今后的数据。

 

今天主要对enable novalidate/disable novalidate 做个简单测试。

 

SQL> create table pk1(id number(2),name varchar2(8));

表已创建。

SQL> insert into pk1 values(1,'abc');

已创建 1 行。

SQL> alter table pk1 add constraint pk_pk1 primary key(id);   --设置主键约束。

表已更改。

SQL> create table fr1(id number(2),name varchar2(8));

表已创建。

SQL> insert into fr1 values(1,'abc');

已创建 1 行。

SQL>  alter table fr1 add constraint fk_fr1 foreign key(id) references pk1(id);

表已更改。

 

--设置外键。

测试外键下使用enable novalidate/disable novalidate的情况

 

SQL> insert into fr1 values(2,'aaa');
insert into fr1 values(2,'aaa')
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYS.FK_FR1) - 未找到父项关键字

 

SQL> alter table fr1 disable novalidate constraint fk_fr1;

表已更改。

 

SQL> insert into fr1 values(2,'aaa');

已创建 1 行。

 

SQL> select * from fr1;

        ID NAME
---------- --------
         1 abc
         2 aaa

 

SQL> alter table fr1 enable validate constraint fk_fr1;
alter table fr1 enable validate constraint fk_fr1
                                           *
第 1 行出现错误:
ORA-02298: 无法验证 (SYS.FK_FR1) - 未找到父项关键字

SQL> alter table fr1 enable novalidate constraint fk_fr1;

表已更改。

 

--从上面测试结果可见enable novalidate和disable novalidate在外键约束下使用是好用的。

 

 

测试主键下使用enable novalidate/disable novalidate的情况

 

SQL> select * from pk1;

        ID NAME
---------- --------
         1 abc

 

SQL>  alter table pk1 disable novalidate primary key;
 alter table pk1 disable novalidate primary key
*
第 1 行出现错误:
ORA-02297: 无法禁用约束条件 (SYS.PK_PK1) - 存在相关性

 

SQL> alter table fr1 disable validate constraint fk_fr1;

表已更改。

 

SQL> alter table pk1 disable novalidate primary key;

表已更改。

 

SQL> insert into pk1 values(1,'ccc');

已创建 1 行。

 

SQL> select * from pk1;

        ID NAME
---------- --------
         1 abc
         1 ccc

 

SQL> alter table pk1  enable novalidate primary key;
alter table pk1  enable novalidate primary key
*
第 1 行出现错误:
ORA-02437: 无法验证 (SYS.PK_PK1) - 违反主键

 

SQL> select INDEX_NAME,TABLE_NAME,TABLE_OWNER from user_indexes where index_name
='PK_PK1';

未选定行

 

--主键对应的索引PK_PK1已经不在了。

 

SQL> create index cc on pk1(id);

索引已创建。

 

SQL> alter table pk1 enable novalidate primary key;

表已更改。

--OK了。

 

--从上面的测试结果看出：novalidate在非主键，唯一键时可以正常工作。在对主键，唯一键使用时需要先创建相关索引,再使用novalidate。Primary and unique keys must use nonunique indexes。

 

举一个使用novalidate的案例：

 

给一个有数据的表增加一个非空的列。要求：需要在该表上增加一个字段status，但该字段不能为空，历史数据可以为空。最后将历史数据的该新加字段统一置为1

 

SQL> desc pk1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------

 ID                                        NOT NULL NUMBER(2)
 NAME                                               VARCHAR2(8)

 

SQL> select * from pk1;

        ID NAME
---------- --------
         1 abc
         2 cba

 

SQL> alter table pk1 add status number(1);

表已更改。

 

SQL> alter table pk1 modify status not null enable novalidate;

表已更改。

 

SQL> select * from pk1;

        ID NAME         STATUS
---------- -------- ----------
         1 abc
         2 cba

 

SQL> update pk1 set status=1;

已更新2行。

 

SQL> commit;

提交完成。

 

SQL> select * from pk1;

        ID NAME         STATUS
---------- -------- ----------
         1 abc               1
         2 cba               1

SQL> insert into pk1 values(3,'bca',null);
insert into pk1 values(3,'bca',null)
                               *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SYS"."PK1"."STATUS")