约束enable disable novalidate 测试

简介

      今天研发问了这样一个问题，他说在测试环境中发现一个问题。有主外键约束的两张测试表是由逻辑dmp备份导入的，导入时并没有违反约束的告警，但是当手工失效主表的外键约束后，无法在手工enable原来的外键约束了，请我查一下原因。

1、搭建测试环境

-- 11g R2 11.2.0.4.0

-- 创建测试表emp1，dept1
SQL> create table emp1 as select * from emp;
Table created.

SQL> create table dept1 as select * from dept;
Table created.

--添加外键约束；
SQL> alter table dept1 add constraint pk_dept1 primary key (deptno);
Table altered.
SQL> alter table emp1 add constraint fk_dept1_emp1 foreign key (deptno) references dept1(deptno);
Table altered.

SQL> set linesize 200
SQL> col owner for a20
SQL> select owner,
       constraint_name,
       constraint_type,
       table_name,
       status,
       validated
  from sys.dba_constraints a
 where a.table_name in ('DEPT1', 'EMP1');

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS   VALIDATED
-------------------- ------------------------------ - ------------------------------     -------- -------------
SCOTT                FK_DEPT1_EMP1                  R EMP1                           ENABLED  VALIDATED
SCOTT                PK_DEPT1                       P DEPT1                          ENABLED  VALIDATED

2、模拟实际情况

--我们先失效外键约束，然后在主表中insert一条违反约束的数据看会怎样？
--删除子表中deptno=50的row，这时如果往主表中insert一条deptno=50记录，这两张表中的数据就不符合约束要求了

SQL> alter table emp1 disable constraint fk_dept1_emp1;
Table altered.

SQL> delete from dept1 where deptno=50;
1 row deleted.

SQL> insert into emp1 values(7935,'TX9AB','DBA',7839,to_date('1982/1/23','yyyy/mm/dd'),8000,500,50);
1 row created.

SQL> commit;
Commit complete.

--尝试enable emp1和dept1之间的外键约束

SQL> alter table emp1 enable constraint fk_dept1_emp1;
alter table emp1 enable constraint fk_dept1_emp1
                                   *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPT1_EMP1) - parent keys not found

--那么问题来了，既然数据不符合约束的条件那之前的约束是怎么enable的呢？

3、使用enable novalidate constraint使数据库跳过对历史数据一致性的检验

SQL> alter table emp1 enable novalidate constraint fk_dept1_emp1;
Table altered.

--查看约束有效性
SQL> select owner,
       constraint_name,
       constraint_type,
       table_name,
       status,
       validated
  from sys.dba_constraints a
 where a.table_name in ('DEPT1', 'EMP1');  

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS   VALIDATED
-------------------- ------------------------------ - ------------------------------ -------- -------------
SCOTT                FK_DEPT1_EMP1                  R EMP1                           ENABLED  NOT VALIDATED <--注这里虽然status=enabled，有效性却标为失效！
SCOTT                PK_DEPT1                       P DEPT1                          ENABLED  VALIDATED


--问题已经清楚了，后来让研发查了下主表中确实存在大量不满足主外键约束的记录。

小结

      对于要建立主外键约束的表无论是主表还是子表，只要是包含了不符合约束条件的数据。直接enable constraint都会抛出违反约束的错误，这时为了保证当前或是未来时间点内的约束一致性可以使用enable novalidate constraint使数据库跳过对历史数据一致性的检验，来保证今后数据的一致性。值得注意的是，使用了enable novalidate constraint后，使用通用的逻辑exp、imp、expdp、impdp导入有数据一致性问题（历史数据不一致）的表时不会报错。但是手工disable constraint再enable constraint将提示违背约束。



                                                                      --End

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26727294/viewspace-1802686/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26727294/viewspace-1802686/