• Oracle添加含有脏数据的约束


    需求:

      一个表的唯一约束被禁用期间,有脏数据进来,当启用约束时失败。

    环境:

    -bash-4.1$ uname -a
    Linux dbtest1 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
    
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    

     测试表为scott.test,含有的数据为:

    SQL> select * from scott.test;
    
            ID      VALUE
    ---------- ----------
             1          2
             1          3
             1          2
             3          2
             2
    

     尝试对value列添加唯一约束:

    SQL> alter table scott.test add   constraint uni_test unique(value);
    alter table scott.test add   constraint uni_test unique(value)
                                            *
    ERROR at line 1:
    ORA-02299: cannot validate (SCOTT.UNI_TEST) - duplicate keys found
    

      这里发现由于value列存在重复数据,直接添加唯一约束会失败。查看官方文档中,约束有2个参数,可以配置成只对新数据做约束检查,对已存在旧数据约束不生效,如下:

    You can specify that a constraint is enabled (ENABLE) or disabled (DISABLE). If a constraint is enabled, data is checked as it is entered or updated in the database, and data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform can be allowed to enter the database.

    Additionally, you can specify that existing data in the table must conform to the constraint (VALIDATE). Conversely, if you specify NOVALIDATE, you are not ensured that existing data conforms.

    An integrity constraint defined on a table can be in one of the following states:

    • ENABLEVALIDATE

    • ENABLENOVALIDATE

    • DISABLEVALIDATE

    • DISABLENOVALIDATE

    来源http://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11538

      根据上面的描述,需要使用的为ENABLE,NOVALIDATE这组参数,直接创建该约束会报错,如下:

    SQL> alter table scott.test add   constraint uni_test unique(value) enable novalidate;
    alter table scott.test add   constraint uni_test unique(value) enable novalidate
                                            *
    ERROR at line 1:
    ORA-02299: cannot validate (SCOTT.UNI_TEST) - duplicate keys found
    

      需要在该列上先创建一个普通索引,然后再添加约束,如下:

    SQL> create index scott.test_idx on scott.test(value);
    
    Index created.
    
    SQL> alter table scott.test add   constraint uni_test unique(value) enable novalidate;
    
    Table altered.
    

      检测约束的效果:

    SQL> select * from scott.test;
    
            ID      VALUE
    ---------- ----------
             1          2
             1          3
             1          2
             3          2
             2
    
    SQL> insert into scott.test values (3,3);
    insert into scott.test values (3,3)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.UNI_TEST) violated
    
    
    SQL> update scott.test set value = 3 where id = 3;
    update scott.test set value = 3 where id = 3
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.UNI_TEST) violated
    
    
    SQL> insert into scott.test values (4,4);
    
    1 row created.
    

      从上可以看到,该唯一约束添加后,新添加的数据必须符合唯一约束;旧的数据唯一性不做验证,但是唯一列做的update操作是需要做验证的。

    注:

      1. 除了唯一约束,其它约束也可以设置这两个属性。

      2. 删除该约束的时候要先删除对应的索引,然后再删除该约束,否则删除约束操作会失败。

  • 相关阅读:
    边长为n的正六边形
    simsimi无限次数api微信公众平台
    仿QQ空间送礼物功能
    asp.net动态输出404
    瀑布流布局并实现自动加载
    微信浏览器的HTTP_USER_AGENT
    AutoIt学习系列:目录
    PDA开发系列:Google地图接口
    WinCE、Windows Mobile GPRS连接类
    PDA开发系列:数据库的选择
  • 原文地址:https://www.cnblogs.com/opalyao/p/4249515.html
Copyright © 2020-2023  润新知