oracle中constraint的enable validate、enable novalidate、disable validate、dis...

denglj

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

1、概念

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

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

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

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

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

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));

Tablecreated.

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

1 rowcreated.

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

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

Tablealtered.

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

  2                  name varchar2(20));

Tablecreated.

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

1 rowcreated.

--给f_table表增加外键

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

Tablealtered.

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

insertinto f_table values (2,'liujun')

*

ERROR atline 1:

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

found

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

--disablenovalidate禁用完整性约束ORA1.FK_FTABLE_ID

SQL>alter table f_table disable novalidate constraint fk_ftable_id;

Tablealtered.

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

1 rowcreated.

SQL>select * from f_table;

        ID NAME

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

         1 alina

         2 liujun

SQL>alter table f_table enable validate constraint fk_ftable_id;

altertable f_table enable validate constraint fk_ftable_id

                                              *

ERROR atline 1:

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

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

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

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

SQL>alter table f_table enable novalidate constraint fk_ftable_id;

Tablealtered.

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

2.2、主键测试

SQL>select * from p_table;

        ID NAME

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

         1 leo

SQL>alter table p_table disable novalidate primary key;

altertable p_table disable novalidate primary key

*

ERROR atline 1:

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

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

--先使用disablevalidate使外键失效

SQL>alter table f_table disable validate constraint fk_ftable_id;

altertable f_table disable validate constraint fk_ftable_id

                                               *

ERROR atline 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 rowdeleted.

SQL>alter table f_table disable validate constraint fk_ftable_id;

Tablealtered.

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

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

updatef_table set id=2 where name='alina'

*

ERROR atline 1:

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

disabledand validated

SQL>delete from f_table where id=1;

deletefrom f_table where id=1

*

ERROR atline 1:

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

disabledand validated

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

insertinto f_table values (2,'liujun')

*

ERROR atline 1:

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

disabledand validated

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

no rowsselected

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

--外键disablevalidate后，处理主键.

SQL>alter table p_table disable novalidate primary key;

Tablealtered.

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

1 rowcreated.

SQL>select * from p_table;

        ID NAME

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

         1 leo

         1 paul

SQL>alter table p_table enable novalidate primary key;

altertable p_table enable novalidate primary key

*

ERROR atline 1:

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

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

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

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

no rowsselected

--p_table表重新创建索引

SQL>create index pk_ptable_id02 on p_table (id);

Indexcreated.

SQL>select * from p_table;   

        ID NAME

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

         1 leo

         1 paul

SQL>alter table p_table enable novalidate primary key;

Tablealtered.

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

SQL>select index_name,index_type,table_name,table_type,table_owner fromuser_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在外键可以正常激活，但对于主键需要先创建相关索引才能重新激活.