ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

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

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-10 17:37:36 0 删除 编辑
建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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24496749/viewspace-723267/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Autotrace 用法总结
请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    206940