Oracle约束enable validate时…

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

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

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 constraintpk_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 constraintpk_t1

SQL>exceptions intoexceptions;               ------  将异常数据装入异常表

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. 修正异常数据后，将索引生效。

备注：
启用约束:
enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束.
enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.

禁用约束:
disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作.
disable validate :关闭约束,删除索引,不能对表进行 插入/更新/删除等操作.