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) violated

SQL> insert into t values(2);

1 row created.

SQL> rollback;

Rollback complete.