文档课题：oracle中constraint的enable validate、enable novalidate、disable validate、disable novalidate解析.

1、概念

oracle完整性约束为以下四种状态：

enable validate：开启约束检查所有存在的数据

enable novalidate：开启约束不检查已存在的数据

disable validate：禁用约束，删除约束上的索引并禁止对约束列进行任何dml操作

disable novalidate：与禁用约束一样，不检查以后的数据

2、测试

环境：oracle 19.12 + 多租户

2.1、外键测试

SQL> conn ora1/ora1@orclpdb;                         

Connected.

SQL> create table p_table (id   number(10,2),

  2                   name varchar2(20));

 

Table created.

 

SQL> insert into p_table values (1,'leo');

 

1 row created.

--给p_table表增加主键约束.

SQL> alter table p_table add constraint pk_ptable_id primary key (id);

 

Table altered.

 

SQL> create table f_table(id   number(10,2),

  2                  name varchar2(20));

 

Table created.

 

SQL> insert into f_table values (1,'alina');

 

1 row created.

--给f_table表增加外键

SQL> alter table f_table add constraint fk_ftable_id foreign key (id) references p_table(id);

 

Table altered.

 

SQL> insert into f_table values (2,'liujun');

insert into f_table values (2,'liujun')

*

ERROR at line 1:

ORA-02291: integrity constraint (ORA1.FK_FTABLE_ID) violated - parent key not

found

 

说明：向f_table表insert数据，报错：在父表中未找到父项关键字，违反完整性约束条件ORA1.FK_FTABLE_ID.

--disable novalidate禁用完整性约束ORA1.FK_FTABLE_ID

SQL> alter table f_table disable novalidate constraint fk_ftable_id;

 

Table altered.

 

SQL> insert into f_table values (2,'liujun');

 

1 row created.

 

SQL> select * from f_table;

 

        ID NAME

---------- --------------------

         1 alina

         2 liujun

 

SQL> alter table f_table enable validate constraint fk_ftable_id;

alter table f_table enable validate constraint fk_ftable_id

                                               *

ERROR at line 1:

ORA-02298: cannot validate (ORA1.FK_FTABLE_ID) - parent keys not found

 

说明：添加完数据后，enable validate重新激活约束FK_FTABLE_ID，报错无法验证ORA1.FK_FTABLE_ID，未在父项中找到关键字.

异常原因：enable validate会检查已存在的数据是否满足完整性约束.

--使用enable novalidate激活约束，旨在不检查此前存在的数据.

SQL> alter table f_table enable novalidate constraint fk_ftable_id;

 

Table altered.

 

小结：外键可直接通过enable novalidate激活失效的完整性约束.

2.2、主键测试

SQL> select * from p_table;

 

        ID NAME

---------- --------------------

         1 leo

 

SQL> alter table p_table disable novalidate primary key;

alter table p_table disable novalidate primary key

*

ERROR at line 1:

ORA-02297: cannot disable constraint (ORA1.PK_PTABLE_ID) - dependencies exist

 

说明：disable novalidate无法直接禁用主键，因为存在外键引用该主键.

--先使用disable validate使外键失效

SQL> alter table f_table disable validate constraint fk_ftable_id;

alter table f_table disable validate constraint fk_ftable_id

                                                *

ERROR at line 1:

ORA-02298: cannot validate (ORA1.FK_FTABLE_ID) - parent keys not found

 

SQL> select * from f_table;

 

        ID NAME

---------- --------------------

         1 alina

         2 liujun

 

SQL> delete from f_table where id=2;

 

1 row deleted.

 

SQL> alter table f_table disable validate constraint fk_ftable_id;

 

Table altered.

--使用disable validate后，验证索引以及是否禁止对约束列的任何dml操作

SQL> update f_table set id=2 where name='alina';

update f_table set id=2 where name='alina'

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (ORA1.FK_FTABLE_ID)

disabled and validated

 

SQL> delete from f_table where id=1;

delete from f_table where id=1

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (ORA1.FK_FTABLE_ID)

disabled and validated

 

SQL> insert into f_table values (2,'liujun');

insert into f_table values (2,'liujun')

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (ORA1.FK_FTABLE_ID)

disabled and validated

SQL> select index_name,index_type,table_name,table_type from user_indexes where table_name='F_TABLE';

 

no rows selected

说明：使用disable validate后，索引被删除，同时约束列也无法执行dml操作.

--外键disable validate后，处理主键.

SQL> alter table p_table disable novalidate primary key;

 

Table altered.

 

SQL> insert into p_table values (1,'paul');

 

1 row created.

 

SQL> select * from p_table;

 

        ID NAME

---------- --------------------

         1 leo

         1 paul

 

SQL> alter table p_table enable novalidate primary key;

alter table p_table enable novalidate primary key

*

ERROR at line 1:

ORA-02437: cannot validate (ORA1.PK_PTABLE_ID) - primary key violated

 

说明：主键列无法使用enable novalidate直接激活主键.

--主键对应的索引PK_PTABLE_ID查询不到

SQL> select index_name,index_type,table_name,table_type,table_owner from user_indexes where table_name='P_TABLE';

 

no rows selected

--p_table表重新创建索引

SQL> create index pk_ptable_id02 on p_table (id);

 

Index created.

 

SQL> select * from p_table;   

 

        ID NAME

---------- --------------------

         1 leo

         1 paul

 

SQL> alter table p_table enable novalidate primary key;

 

Table altered.

 

说明：添加索引后，主键成功被激活.

SQL> select index_name,index_type,table_name,table_type,table_owner from user_indexes where table_name='P_TABLE'

 

INDEX_NAME           INDEX_TYPE      TABLE_NAME      TABLE_TYPE  TABLE_OWNER

-------------------- --------------- --------------- ----------- ---------------

PK_PTABLE_ID02       NORMAL          P_TABLE         TABLE       ORA1

 

总结：novalidate在外键可以正常激活，但对于主键需要先创建相关索引才能重新激活.

 

参考网址：

https://blog.csdn.net/weixin_35259908/article/details/116539731