Ask Hoegh（8）——都是唯一索引惹的祸

问：

最近有个项目提出新需求，其中一个业务表的主键字段需要修改；

麻烦的是，对于新的主键字段，表的已有数据中包含重复数据，

请问这个需求可以实现吗？具体该如何操作？

---

答：

首先这个需求肯定是可以实现的，在具体演示如何实现之前，我们先回顾一下针对Oracle约束的一组关键字 Ena ble/Disable/Validate/Novalidate。

一、Enable/Disable/Validate/Novalidate

	Validate		Novalidate
	已有记录	新增/删除记录	已有记录	新增/删除记录
Enable	Yes	Yes	No	Yes
Disable	Yes	No	No	No

Validate确保已有数据符合约束；

Novalidate不必考虑已有数据是否符合约束。

 

除非Novalidate被指定，Enable默认Validate；

除非Validate被指定，Disable默认Novalidate。

 

Enable Validate与Enable相同，检查已有记录和新增记录，确保都符合约束；

Enable Novalidate 允许已有记录不必满足约束条件，但新增/修改的记录必须满足；

Disable Validate禁用约束，删除约束上的索引，不允许修改任何被约束的记录；

Disable Novalidate与Disable相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。

     针对我们的问题，我们需要关注的是 Enable Novalidate，也就是说 允许已有记录不必满足约束条件，但新增 / 修改的记录必须满足。

二、创建测试表

点击(此处)折叠或打开

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> insert into hoegh values(1,1,1);
    
18. 
    
19. 1 row inserted
    
20. 
    
21. SQL> insert into hoegh values(1,2,1);
    
22. 
    
23. 1 row inserted
    
24. 
    
25. SQL> commit;
    
26. 
    
27. Commit complete
    
28. 
    
29. SQL>
    
30. SQL> select * from hoegh;
    
31. 
    
32.          X Y Z
    
33. ---------- ---------- ----------
    
34.          1 1 1
    
35.          1 2 1

三、针对非主键约束的Enable Novalidate

     我们以check约束为例看一下如何使用 Enable Novalidate关键字。

点击(此处)折叠或打开

1. SQL> alter table hoegh add constraint check_z check(z>1) enable validate;
   
2. 
   
3. alter table hoegh add constraint check_z check(z>1) enable validate
   
4. 
   
5. ORA-02293: 无法验证 (HOEGH.CHECK_Z) - 违反检查约束条件
   
6. 
   
7. SQL> alter table hoegh add constraint check_z check(z>1) enable novalidate;
   
8. 
   
9. Table altered
   
10. 
    
11. SQL> insert into hoegh values(2,1,1);
    
12. 
    
13. insert into hoegh values(2,1,1)
    
14. 
    
15. ORA-02290: 违反检查约束条件 (HOEGH.CHECK_Z)
    
16. 
    
17. SQL>

      我们可以看到，第一次我们尝试添加 Enable Validate的check约束，但是已有数据中违反了该约束，因此添加失败；第二次尝试添加Enable Novalidate的check约束，成功了。
     同样的，外键约束也可以参考上述语句来添加或修改，那么主键约束可以吗？

四、针对主键约束的Enable Novalidate

     1.查看当前约束和索引

点击(此处)折叠或打开

1. SQL> select constraint_name,constraint_type FROM user_constraints where table_name='HOEGH';
   
2. 
   
3. CONSTRAINT_NAME CONSTRAINT_TYPE
   
4. ------------------------------ ---------------
   
5. PK_HOEGH P
   
6. 
   
7. SQL>
   
8. SQL> select index_name,index_type,uniqueness from user_indexes where table_name='HOEGH';
   
9. 
   
10. INDEX_NAME INDEX_TYPE UNIQUENESS
    
11. ------------------------------ --------------------------- ----------
    
12. PK_HOEGH NORMAL UNIQUE
    
13. 
    
14. SQL>

      2.删除当前主键约束和索引

点击(此处)折叠或打开

1. SQL> alter table hoegh drop constraint PK_HOEGH cascade drop index;
   
2. 
   
3. Table altered
   
4. 
   
5. SQL> select constraint_name,constraint_type FROM user_constraints where table_name='HOEGH';
   
6. 
   
7. CONSTRAINT_NAME CONSTRAINT_TYPE
   
8. ------------------------------ ---------------
   
9. 
   
10. SQL> select index_name,index_type,uniqueness from user_indexes where table_name='HOEGH';
    
11. 
    
12. INDEX_NAME INDEX_TYPE UNIQUENESS
    
13. ------------------------------ --------------------------- ----------
    
14. 
    
15. SQL>

      3.尝试添加主键约束并设置Enable Novalidate

点击( 此处 )折叠或打开

1. SQL>
   
2. SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;
   
3. 
   
4. alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate
   
5. 
   
6. ORA-02437: 无法验证 (HOEGH.PK_HOEGH) - 违反主键
   
7. 
   
8. SQL>
   
9. SQL> select * from hoegh;
   
10. 
    
11.          X Y Z
    
12. ---------- ---------- ----------
    
13.          1 1 1
    
14.          1 2 1
    
15. 
    
16. SQL>

      我们看到添加主键约束失败了，提示“ORA-02437: 无法验证 (SCOTT.PK_HOEGH) - 违反主键”，也就是说已有的数据不满足新的主键约束；
      虽然我们设置了 Novalidate ，但是和我们预想的结果不一致，数据库仍 然检验了已有数据。

     我们知道主键约束=唯一约束+非空约束，那么这个是不是和唯一约束有关系呢？
     接下来，我们尝试添加一个唯一约束，同时设置 Enable  Nov alidate。
点击( 此处 )折叠或打开

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>

   到此，这个问题得到了圆满解决。大家如果有更好的方法欢迎不吝赐教。


                                                                                                                                                                                            ~~~~~~~ the end~~~~~~~~~
                                                                                                                                                                                                               hoegh
                                                                                                                                                                                                           2016.06.17

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

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