最近项目中要求对一张业务表修改主键，前提是之前的业务数据不能受到影响。我想到了针对约束的enable novalidate，也就是说允许已有记录不必满足约束条件，但新增/修改的记录必须满足。

我们先回顾一下关于启用和禁用约束的4种组合：
启用约束：
enable( validate)：启用约束,创建索引,对已有及新加入的数据执行约束。
enable novalidate：启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据。

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

我们针对主键约束的enable novalidate做如下测试。

1.创建测试表

点击(此处)折叠或打开

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table hoegh(x number,y number,z number,
2 constraint pk_hoegh primary key(x,y));
Table created
SQL>
SQL> insert into hoegh values(1,1,1);
1 row inserted
SQL> insert into hoegh values(1,2,1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from hoegh;
X Y Z
---------- ---------- ----------
1 1 1
1 2 1
SQL>

2.删除原有主键

点击(此处)折叠或打开

SQL> alter table hoegh drop constraint PK_HOEGH cascade drop index;
Table altered

3.添加新主键并设置Enable Novalidate，报错

点击(此处)折叠或打开

SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;
alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate
ORA-02437: 无法验证 (HOEGH.PK_HOEGH) - 违反主键
SQL>

我们看到添加主键约束失败了，提示“ORA-02437: 无法验证 (HOEGH.PK_HOEGH) - 违反主键”，也就是说已有的数据不满足新的主键约束；虽然我们设置了Novalidate，但是和我们预想的结果不一致，数据库仍然检验了已有数据。
我们知道主键约束=唯一约束+非空约束，那么这个是不是和唯一约束有关系呢？接下来，我们尝试添加一个唯一约束，同时设置Enable Novalidate。

点击(此处)折叠或打开

SQL> alter table hoegh add constraint unq_xz unique(x,z) enable novalidate;
alter table hoegh add constraint unq_xz unique(x,z) enable novalidate
ORA-02299: 无法验证 (HOEGH.UNQ_XZ) - 找到重复关键字
SQL>

我们看到也失败了，看来根源就在这里。创建主键时系统缺省还要同时创建一个唯一索引，novalidate只是使主键约束不对已经存在的数据进行检验，并不对创建唯一索引进行控制，所以导致失败。
那么如何解决这个问题呢？

4.创建普通索引，然后添加主键约束并设置Enable Novalidate，成功

我们可以在创建主键前先给该字段建个普通非唯一索引，这样建主键时系统就会自动利用这个普通索引了。
点击(此处)折叠或打开

SQL>
SQL> create index idx_xz on hoegh(x,z);
Index created
SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;
Table altered
SQL> insert into hoegh values(1,3,1);
insert into hoegh values(1,3,1)
ORA-00001: 违反唯一约束条件 (HOEGH.PK_HOEGH)
SQL> select * from hoegh;
X Y Z
---------- ---------- ----------
1 1 1
1 2 1
SQL>
SQL> insert into hoegh values(1,3,2);
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> select * from hoegh;
X Y Z
---------- ---------- ----------
1 1 1
1 2 1
1 3 2
SQL>

我们看到，已有的记录不受新约束的影响，而新增记录必须满足新的主键约束。

5.结论

从上面的测试结果看出,enable novalidate在针对主键约束或者唯一约束使用时需要先创建相关索引,再使用enable novalidate。

~~~~~~~ the end~~~~~~~~~
hoegh
2016.06.19

ITPub博客

主键约束设置Enable Novalidate时报错

1.创建测试表

2.删除原有主键

3.添加新主键并设置Enable Novalidate，报错

4.创建普通索引，然后添加主键约束并设置Enable Novalidate，成功

5.结论

最新文章