disabled and validated 不能DML

Inserting Rows Not Allowed in Table with DISABLED and VALIDATED Constraint (Doc ID 139337.1)

​编辑To Bottom

---

Problem Description: ==================== You create a table with a referential integrity constraint (a foreign key constraint) referencing a view constraint. SQL> CREATE TABLE TEST20 ( seq number, test char(50), id number REFERENCES DEPT20_EMPLOYEES RELY DISABLE VALIDATE); Table created. SQL> select * from test20; no rows selected SQL> select id from dept20_employees; ID ---------- 201 202 Attempting to insert into this table results in an ORA-25128 "No insert/ update/delete on table with constraint (%s.%s) disabled and validated" error. SQL> insert into test20 values (1,'TEST',201); insert into test20 values (1,'TEST',201) * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (HR.SYS_C003510) disabled and validated SQL> insert into test20 values (1,'TEST',203); insert into test20 values (1,'TEST',203) * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (HR.SYS_C003510) disabled and validated Solution Description: ===================== Set the state of the Foreign Key constraint to DISABLE NOVALIDATE. For example: SQL> alter table test20 modify constraint sys_c003510 NOVALIDATE; Table altered. SQL> insert into test20 values (1,'TEST',203); 1 row created. Explanation: ============ The view constraint referenced by table TEST20 is necessarily a constraint in DISABLE NOVALIDATE state, since it is required by view constraint definition: SQL> CREATE VIEW DEPT20_EMPLOYEES ( id PRIMARY KEY RELY DISABLE NOVALIDATE , first_name, last_name, email) AS SELECT employee_id, first_name, last_name, email FROM EMPLOYEES WHERE department_id = 20; View created. Therefore any referencing constraint needs to be in DISABLE NOVALIDATE as well. 文档课题：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在外键可以正常激活，但对于主键需要先创建相关索引才能重新激活.