表里有重复数据加唯一索引
先建普通索引，然后建唯一性约束，必须带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博客

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

最新文章