Oracle约束enable validate时数据检查

Oracle约束按约束状态又可分为disable novalidate,disable validate,enable novalidate,enable validate. 其中disable/enable代表是否启用约束，validate/novalidate代表启用约束时是否对表中原有数据作检查。

      这里模拟生产环境中的情况，先建一张没有约束的表，等有一定数据后建立enable novalidate约束，进而执行enable validate来规范表中所有数据，具体如下：

      1. 创建原始表及数据

          SQL> create table t1(

                2  id number,

                3  name varchar2(20));

          SQL> insert into t1 values(1,'A');

          SQL> insert into t1 values(1,'B');

          SQL> insert into t1 values(2,'C');

          SQL> insert into t1 values(3,'D');

          SQL> insert into t1 values(3,'E');

          SQL> insert into t1 values(4,'F');

      2. 在表上创建enable novalidate主键

          SQL> alter table t1 add constraint pk_t1 primary key (id)

                2  deferrable enable novalidate;

          此时主键已经生效，但源表中包含不符合约束的数据

      3. 尝试将约束状态更改为 enable validate，却发现原有数据有问题

          SQL> alter table t1 enable validate constraint pk_t1;

          alter table t1 enable validate constraint pk_t1

          *

          第 1 行出现错误:

          ORA-02437: 无法验证 (SCOTT.PK_T1) - 违反主键

      4. 找出违反约束的数据，这里采用Exceptions表保存异常数据

          SQL>@$ORACLE_HOME/rdbms/admin/utlexpt1.sql    ------  建立异常数据保存表

          SQL>alter table t1 enable validate constraint pk_t1 exceptions into exceptions;                                     ------  将异常数据装入异常表

      5. 根据异常表，找出对应源表中的异常数据

           SQL> select id,name from t1 where rowid in(

                 2  select row_id from exceptions);

                 ID NAME

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

                         1 A

                         1 B

                         3 D

                         3 E

      6. 修正异常数据后，将索引生效。