主键约束设置Enable Novalidate时报错

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

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

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

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

1.创建测试表

点击( 此处 )折叠或打开

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. ----------------------------------------------------------------
   
5. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
   
6. PL/SQL Release 10.2.0.4.0 - Production
   
7. CORE    10.2.0.4.0    Production
   
8. 
   
9. TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
   
10. NLSRTL Version 10.2.0.4.0 - Production
    
11. 
    
12. SQL> create table hoegh(x number,y number,z number,
    
13.   2 constraint pk_hoegh primary key(x,y));
    
14. 
    
15. Table created
    
16. 
    
17. SQL>
    
18. SQL> insert into hoegh values(1,1,1);
    
19. 
    
20. 1 row inserted
    
21. 
    
22. SQL> insert into hoegh values(1,2,1);
    
23. 
    
24. 1 row inserted
    
25. 
    
26. SQL> commit;
    
27. 
    
28. Commit complete
    
29. 
    
30. SQL> select * from hoegh;
    
31. 
    
32.          X Y Z
    
33. ---------- ---------- ----------
    
34.          1 1 1
    
35.          1 2 1
    
36. 
    
37. SQL>

2.删除原有主键

点击( 此处 )折叠或打开

1. SQL> alter table hoegh drop constraint PK_HOEGH cascade drop index;
   
2. 
   
3. Table altered

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

点击( 此处 )折叠或打开

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

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

点击(此处)折叠或打开

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

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

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

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

1. SQL>
   
2. SQL> create index idx_xz on hoegh(x,z);
   
3. 
   
4. Index created
   
5. 
   
6. SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;
   
7. 
   
8. Table altered
   
9. 
   
10. SQL> insert into hoegh values(1,3,1);
    
11. 
    
12. insert into hoegh values(1,3,1)
    
13. 
    
14. ORA-00001: 违反唯一约束条件 (HOEGH.PK_HOEGH)
    
15. 
    
16. SQL> select * from hoegh;
    
17. 
    
18.          X Y Z
    
19. ---------- ---------- ----------
    
20.          1 1 1
    
21.          1 2 1
    
22. 
    
23. SQL>
    
24. SQL> insert into hoegh values(1,3,2);
    
25. 
    
26. 1 row inserted
    
27. 
    
28. SQL> commit;
    
29. 
    
30. Commit complete
    
31. 
    
32. SQL>
    
33. SQL> select * from hoegh;
    
34. 
    
35.          X Y Z
    
36. ---------- ---------- ----------
    
37.          1 1 1
    
38.          1 2 1
    
39.          1 3 2
    
40. 
    
41. SQL>

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

5.结论

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

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

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

转载于:http://blog.itpub.net/30162081/viewspace-2120425/