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

人总是难免范傻的。

 

  今天是2013-09-15，有时候学习不 认真就是天大的错误，只要你学习浮躁的对待一件事情，那么这件事情也会同样的对待你。
先看看我悲催的1个小时吧。
问题是这样的，我创建一个表：
SQL> create table amy_salgrade as select * from scott.salgrade;

Table created.
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;

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;

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.

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