添加唯一性约束遇到ORA-02299

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5752483.html

最近为一张大表添加了唯一性约束，因为表以前有很多数据有重复的列，但是客户要求保留，没有办法直接添加唯一性约束。最后记录下这次操作的思路

1.创建表和测试数据
SQL> create table t(id number(5),text varchar2(128));

Table created.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(5)
 TEXT                                               VARCHAR2(128)


SQL> insert into t values(1,'aaa');

1 row created.

SQL> insert into t values(2,'bbb');

1 row created.

SQL> insert into t values(3,'ccc');

1 row created.

SQL> insert into t values(4,'bbb');

1 row created.

SQL> commit;

Commit complete.

SQL> col text for a30
SQL> select * from t;

        ID TEXT
---------- ------------------------------
         1 aaa
         2 bbb
         3 ccc
         4 bbb


2.试着创建约束
SQL> alter table t add constraint t_text_uq unique(text);
alter table t add constraint t_text_uq unique(text)
                             *
ERROR at line 1:
ORA-02299: cannot validate (TEST.T_TEXT_UQ) - duplicate keys found

报错，表中原有数据不符合唯一性约束的规则

3.按照以下步骤：

(1)创建索引
SQL> create index t_idx_text on t(text);

Index created.

SQL> show user
USER is "TEST"

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME      STATUS
------------------------------ --------------------------- ------------------------------ ------------------------------
T_IDX_TEXT                     NORMAL                      TEST                           T          VALID

(2)添加约束
SQL> alter table t add constraint t_text_uq unique(text);
alter table t add constraint t_text_uq unique(text)
                             *
ERROR at line 1:
ORA-02299: cannot validate (TEST.T_TEXT_UQ) - duplicate keys found


SQL> alter table t add constraint t_text_uq unique(text) disable;

Table altered.

SQL> select table_name from dict where table_name like '%CONSTRAINT%';

TABLE_NAME
------------------------------
DBA_CONSTRAINTS
DBA_IAS_CONSTRAINT_EXP
USER_CONSTRAINTS
ALL_CONSTRAINTS

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from USER_CONSTRAINTS;

OWNER                                                        CONSTRAINT_NAME                C TABLE_NAME    STATUS   VALIDATED
------------------------------------------------------------ ------------------------------ - -------------------------------------- --
TEST                                                         T_TEXT_UQ                      U T            DISABLED NOT VALIDATED

(3)
SQL> alter table t modify constraint t_text_uq  enable;
alter table t modify constraint t_text_uq  enable
*
ERROR at line 1:
ORA-02299: cannot validate (TEST.T_TEXT_UQ) - duplicate keys found


SQL> alter table t modify constraint t_text_uq  enable novalidate;

Table altered.

SQL> col OWNER for a10
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from USER_CONSTRAINTS;

OWNER      CONSTRAINT_NAME                C TABLE_NAME                     STATUS   VALIDATED
---------- ------------------------------ - ------------------------------ -------- -------------
TEST       T_TEXT_UQ                      U T                              ENABLED  NOT VALIDATED

4.接着测试
SQL> 
SQL> insert into t values(5,'ddd');

1 row created.

SQL> commit
  2  ;

Commit complete.

SQL> insert into t values(6,'ccc');
insert into t values(6,'ccc')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T_TEXT_UQ) violated


SQL> insert into t values(2,'bbb');
insert into t values(2,'bbb')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T_TEXT_UQ) violated


SQL> insert into t values(2,'11111');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID TEXT
---------- ------------------------------
         1 aaa
         2 bbb
         3 ccc
         4 bbb
         5 ddd
         2 11111

6 rows selected.


说明：创建索引后，也可以直接使用:alter table aa add constraint aa_un unique(email) enable novalidate;
---the end