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