ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表里有重复数据加唯一索引的变通方法

表里有重复数据加唯一索引的变通方法

原创 Linux操作系统 作者:DataKW 时间:2013-06-25 18:29:23 0 删除 编辑
表里有重复数据加唯一索引
先建普通索引,然后建唯一性约束,必须带enable novalidate字段
create  index i_ITEM_CARD on  T_SHOP_GOODS_STOCK_DETAIL(ITEM_CARD);
alter table T_SHOP_GOODS_STOCK_DETAIL add constraint c_ITEM_CARD unique (ITEM_CARD) enable novalidate;
SQL> alter table test_cc modify txn_password not null;
alter table test_cc modify txn_password not null
*
ERROR at line 1:
ORA-02296: cannot enable (BTUPAYPROD.) - null values found
 
QL> create index i_name on test_cc(name);
Index created.
SQL>      alter table test_cc add constraint test_name_un unique(name) enable novalidate;
Table altered.
 
 
为什么创建约束需要先建索引???

QL>  alter table TEST_CC add constraint TEST_NAME_TXN_UN unique (NAME,txn_password) enable novalidate;
 alter table TEST_CC add constraint TEST_NAME_TXN_UN unique (NAME,txn_password) enable novalidate
                                    *
ERROR at line 1:
ORA-02299: cannot validate (BTUPAYPROD.TEST_NAME_TXN_UN) - duplicate keys found
 
SQL>   create index i_test_name_txn on test_cc(NAME,txn_password);
Index created.
SQL> alter table TEST_CC add constraint TEST_NAME_TXN_UN unique (NAME,txn_password) enable novalidate;
Table altered.
 
 
 
 
QL>   insert into test_cc values(2222,'cccccc','cbc','asfd');
  insert into test_cc values(2222,'cccccc','cbc','asfd')
*
ERROR at line 1:
ORA-00001: unique constraint (BTUPAYPROD.TEST_NAME_TXN_UN) violated

SQL> insert into test_cc values(2222,'cccccc','cbc','asfda');
insert into test_cc values(2222,'cccccc','cbc','asfda')
*
ERROR at line 1:
ORA-00001: unique constraint (BTUPAYPROD.TEST_NAME_TXN_UN) violated

SQL> alter table test_cc drop constraint TEST_NAME_TXN_UN;
Table altered.
SQL> insert into test_cc values(2222,'cccccc','cbc','asfda');
1 row created.
SQL> commit;
Commit complete.
SQL>  select * from test_cc;
        ID NAME                 TXN_PASSWORD     LOB_C
---------- -------------------- ---------------- --------------------------------------------------------------------------------
         1 c                    cbc
         3 ccc                  cbc
         2 cc                   cbc
        12 cc                   a
        12 ccb
        12 as                   asdfd            asdffdsf
         1 cccccc               cbc              asfd
      1111 cccccc               cbc              asfd
      2222 cccccc               cbc              asfda
9 rows selected.
SQL>  alter table TEST_CC add constraint TEST_NAME_TXN_UN unique (NAME,txn_password) ;
 alter table TEST_CC add constraint TEST_NAME_TXN_UN unique (NAME,txn_password)
                                    *
ERROR at line 1:
ORA-02299: cannot validate (BTUPAYPROD.TEST_NAME_TXN_UN) - duplicate keys found

SQL> alter table TEST_CC add constraint TEST_NAME_TXN_UN unique (NAME,txn_password) enable novalidate;
Table altered.
SQL> 

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

请登录后发表评论 登录
全部评论

注册时间:2012-08-12

  • 博文量
    132
  • 访问量
    299866