建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.
建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.
