ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
来源:互联网 发布:excel数据如何恢复 编辑:程序博客网 时间:2024/04/30 17:52
人总是难免范傻的。
今天是2013-09-15,有时候学习不 认真就是天大的错误,只要你学习浮躁的对待一件事情,那么这件事情也会同样的对待你。
先看看我悲催的1个小时吧。
问题是这样的,我创建一个表:
SQL> create table amy_salgrade as select * from scott.salgrade;
先看看我悲催的1个小时吧。
问题是这样的,我创建一个表:
SQL> create table amy_salgrade as select * from scott.salgrade;
Table created.
SQL> select * from amy_salgrade;
SQL> select * from amy_salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> create unique index unique_index_salgrade on rhys.amy_salgrade('GRADE') tablespace index_tablespace;
create unique index unique_index_salgrade on rhys.amy_salgrade('GRADE') tablespace index_tablespace
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
出现这个问题是什么原因呢?有重复值?开始弄的我匪夷所思。
既然出问题了,一个一个看呗。
SQL> select rowid,grade,losal,hisal from amy_salgrade;
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> create unique index unique_index_salgrade on rhys.amy_salgrade('GRADE') tablespace index_tablespace;
create unique index unique_index_salgrade on rhys.amy_salgrade('GRADE') tablespace index_tablespace
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
出现这个问题是什么原因呢?有重复值?开始弄的我匪夷所思。
既然出问题了,一个一个看呗。
SQL> select rowid,grade,losal,hisal from amy_salgrade;
ROWID GRADE LOSAL HISAL
------------------ ---------- ---------- ----------
AAAVwhAAHAAAACTAAA 1 700 1200
AAAVwhAAHAAAACTAAB 2 1201 1400
AAAVwhAAHAAAACTAAC 3 1401 2000
AAAVwhAAHAAAACTAAD 4 2001 3000
AAAVwhAAHAAAACTAAE 5 3001 9999
没有重复值啊》
有null值?不对啊!不合理啊。
好了,在看一下这个创建语句。居然我在字段上加了引号。不和语法啊。
问题找到了。
SQL> create unique index unique_index_salgrade on rhys.amy_salgrade(GRADE) tablespace index_tablespace;
------------------ ---------- ---------- ----------
AAAVwhAAHAAAACTAAA 1 700 1200
AAAVwhAAHAAAACTAAB 2 1201 1400
AAAVwhAAHAAAACTAAC 3 1401 2000
AAAVwhAAHAAAACTAAD 4 2001 3000
AAAVwhAAHAAAACTAAE 5 3001 9999
没有重复值啊》
有null值?不对啊!不合理啊。
好了,在看一下这个创建语句。居然我在字段上加了引号。不和语法啊。
问题找到了。
SQL> create unique index unique_index_salgrade on rhys.amy_salgrade(GRADE) tablespace index_tablespace;
Index created.
然后再看看另一种情况,就是null。
这段话非常的经典(个人认为)
The issue arises from Oracle's handling of NULLs (as in the first column order_no which has 2 rows with NULL values).
A NULL in Oracle is "indeterminate". It is an unknown value. Therefore, two NULLs are never equal.
The Unique Index sales_orders_u1 on order_no alone succeeds because the NULL values in the two rows are not compared as they are not equal to each other (in fact, the two rows are not even captured in the index !).
However, when I create Unique Index sales_orders_u2 with an additional column, Oracle actually compares the values in the second column. Although it would seem that if two NULLs are not alike, then we could afford to ignore comparing the second column. However, Oracle does not ignore the values that are not NULLs. If it finds duplicates in these values (as it finds in year_month), the test for Uniqueness fails.
然后再看看另一种情况,就是null。
这段话非常的经典(个人认为)
The issue arises from Oracle's handling of NULLs (as in the first column order_no which has 2 rows with NULL values).
A NULL in Oracle is "indeterminate". It is an unknown value. Therefore, two NULLs are never equal.
The Unique Index sales_orders_u1 on order_no alone succeeds because the NULL values in the two rows are not compared as they are not equal to each other (in fact, the two rows are not even captured in the index !).
However, when I create Unique Index sales_orders_u2 with an additional column, Oracle actually compares the values in the second column. Although it would seem that if two NULLs are not alike, then we could afford to ignore comparing the second column. However, Oracle does not ignore the values that are not NULLs. If it finds duplicates in these values (as it finds in year_month), the test for Uniqueness fails.
Update : If the second column, too, has NULLs and there happen(s) to be any row(s) where *both* the first column (order_no) and the second column (year_month) contain a NULL, that/those rows would not be included in the index and would pass the "Uniqueness" test as Oracle cannot compare rows with all NULLs.
参考:http://hemantoracledba.blogspot.com/2010/02/something-unique-about-unique-indexes.html
参考:http://hemantoracledba.blogspot.com/2010/02/something-unique-about-unique-indexes.html
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
- Oracle创建唯一索引遇到的问题(ORA-01452: 无法 CREATE UNIQUE INDEX; 找到重复的关键字)
- Cannot insert duplicate key row in object 'dbo.DependencyNodeBase' with unique index 'ndx_Dependency
- ORA-02429: cannot drop index used for enforcement of unique/primary key
- ORA-02429: cannot drop index used for enforcement of unique/primary key
- ORA-02298 Parent Keys Not Found?解决办法
- 【Oracle问题集5】ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table
- create unique index与alter .. add constraint .. unique的差异
- 当create index local 遇到 ORA-08104
- create index online 异常处理 ORA-08104
- ORA-02449: unique/primary keys in table referenced by foreign keys
- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- truncate table 错误 ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- 解决ORA-08102: index key not found
- ORA-08102: index key not found
- ORA-12913: Cannot create dictionary managed tablespace
- ODI主键报错--create unique index on flow table
- 黑马程序员_<<线程的一般方法>>
- hadoop配置与问题
- poj 3104 Drying (二分搜索答案)
- 一步步学算法(算法题解)---5
- 页面置换算法
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
- MFC相关函数介绍
- 链表现的栈
- 一个骰子,6面,1个面是 1, 2个面是2, 3个面是3,问平均掷多少次能使1、2、3都至少出现一次。
- Extjs treeGrid分页实例
- 找出数组中出现奇数次的元素
- jQuery验证控件jquery.validate.js使用说明+中文API
- 新的起点,新的开始
- 宏定义