oracle create unique index,ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

建unique索引时遇到ORA-01452错误，如下：SQL> create unique index uk_union_col on CFG_PERF_FORMULA(vendor,version,ne_type,att_enname);

create unique index uk_union_col on CFG_PERF_FORMULA(vendor,version,ne_type,att_enname)

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

SQL> select vendor,version,ne_type,att_enname

2 from cfg_perf_formula

3 group by vendor,version,ne_type,att_enname

4 having count(*)>1;

VENDOR VERSION NE_TYPE ATT_ENNAME

---------- -------------------- ---------- ----------------------------------------------------------------

ZY0000 4 UPQUALIITYFLUCTVALUE

ZY0000 2 BADCOVER_CELL_PER

SQL> /

VENDOR VERSION NE_TYPE ATT_ENNAME

---------- -------------------- ---------- -----------------------------------------------------------------

SQL> create unique index uk_union_col on CFG_PERF_FORMULA(vendor,version,ne_type,att_enname);

Index created

这种错误的处理其实可以考虑几种方法：

数据表里要建的这几列有重复的值，要么

1、删除重复值，然后再建唯一索引

2、就不建唯一索引

3、或指定deferrable novalidate不校验表里已存在数据，只对以后的数据有影响。

如：

SQL> create table t(id number);Table created.SQL> insert into t values(1);1 row created.SQL> insert into t values(1);1 row created.SQL> commit;Commit complete.SQL> alter table t add constraint uk_t unique(id) deferrable novalidate;Table altered.SQL> insert into t values(1);

insert into t values(1)

*

ERROR at line 1:

ORA-00001: unique constraint (ZHAOSS.UK_T) violatedSQL> insert into t values(2);1 row created.SQL> rollback;Rollback complete.