ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Ask Hoegh（8）——都是唯一索引惹的祸

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

原创 Oracle 作者：Hoegh 时间：2016-06-17 14:45:45 0 删除 编辑

问： 

最近有个项目提出新需求，其中一个业务表的主键字段需要修改；
麻烦的是，对于新的主键字段，表的已有数据中包含重复数据，
请问这个需求可以实现吗？具体该如何操作？

答：

首先这个需求肯定是可以实现的，在具体演示如何实现之前，我们先回顾一下针对Oracle约束的一组关键字Enable/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;

  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

  9. TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
  10. NLSRTL Version 10.2.0.4.0 - Production

  12. SQL> create table hoegh(x number,y number,z number,
  13.   2 constraint pk_hoegh primary key(x,y));

  15. Table created

  17. SQL> insert into hoegh values(1,1,1);

  19. 1 row inserted

  21. SQL> insert into hoegh values(1,2,1);

  23. 1 row inserted

  25. SQL> commit;

  27. Commit complete

  29. SQL>
  30. SQL> select * from hoegh;

  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;

  3. alter table hoegh add constraint check_z check(z>1) enable validate

  5. ORA-02293: 无法验证 (HOEGH.CHECK_Z) - 违反检查约束条件

  7. SQL> alter table hoegh add constraint check_z check(z>1) enable novalidate;

  9. Table altered

  11. SQL> insert into hoegh values(2,1,1);

  13. insert into hoegh values(2,1,1)

  15. ORA-02290: 违反检查约束条件 (HOEGH.CHECK_Z)

  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';

  3. CONSTRAINT_NAME CONSTRAINT_TYPE
  4. ------------------------------ ---------------
  5. PK_HOEGH P

  7. SQL>
  8. SQL> select index_name,index_type,uniqueness from user_indexes where table_name='HOEGH';

  10. INDEX_NAME INDEX_TYPE UNIQUENESS
  11. ------------------------------ --------------------------- ----------
  12. PK_HOEGH NORMAL UNIQUE

  14. SQL>

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

点击(此处)折叠或打开

  1. SQL> alter table hoegh drop constraint PK_HOEGH cascade drop index;

  3. Table altered

  5. SQL> select constraint_name,constraint_type FROM user_constraints where table_name='HOEGH';

  7. CONSTRAINT_NAME CONSTRAINT_TYPE
  8. ------------------------------ ---------------

  10. SQL> select index_name,index_type,uniqueness from user_indexes where table_name='HOEGH';

  12. INDEX_NAME INDEX_TYPE UNIQUENESS
  13. ------------------------------ --------------------------- ----------

  15. SQL>

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

点击(此处)折叠或打开
  1. SQL>
  2. SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;

  4. alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate

  6. ORA-02437: 无法验证 (HOEGH.PK_HOEGH) - 违反主键

  8. SQL>
  9. SQL> select * from hoegh;

  11.          X Y Z
  12. ---------- ---------- ----------
  13.          1 1 1
  14.          1 2 1

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

     我们知道主键约束=唯一约束+非空约束，那么这个是不是和唯一约束有关系呢？
     接下来，我们尝试添加一个唯一约束，同时设置Enable Novalidate。
点击(此处)折叠或打开
  1. SQL> alter table hoegh add constraint unq_xz unique(x,z) enable novalidate;

  3. alter table hoegh add constraint unq_xz unique(x,z) enable novalidate

  5. ORA-02299: 无法验证 (HOEGH.UNQ_XZ) - 找到重复关键字

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

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

     我们可以在创建主键前先给该字段建个普通非唯一索引，这样建主键时系统就会自动利用这个普通索引了。
点击(此处)折叠或打开
  1. SQL>
  2. SQL> create index idx_xz on hoegh(x,z);

  4. Index created

  6. SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;

  8. Table altered

  10. SQL> insert into hoegh values(1,3,1);

  12. insert into hoegh values(1,3,1)

  14. ORA-00001: 违反唯一约束条件 (HOEGH.PK_HOEGH)

  16. SQL> select * from hoegh;

  18.          X Y Z
  19. ---------- ---------- ----------
  20.          1 1 1
  21.          1 2 1

  23. SQL>
  24. SQL> insert into hoegh values(1,3,2);

  26. 1 row inserted

  28. SQL> commit;

  30. Commit complete

  32. SQL>
  33. SQL> select * from hoegh;

  35.          X Y Z
  36. ---------- ---------- ----------
  37.          1 1 1
  38.          1 2 1
  39.          1 3 2

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


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

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

0
0
上一篇： 12c OCP题库解析060-7 Oracle 12c 安全性与合规性的新特性
下一篇： 主键约束设置Enable Novalidate时报错
请登录后发表评论 登录
全部评论
Hoegh

注册时间：2015-03-13

  • 博文量
    132
  • 访问量
    2219938

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593