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

有時候學習不 認真就是天大的錯誤，只要你學習浮躁的對待一件事情，那麼這件事情也會同樣的對待你。
先看看我悲催的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.

SPFILE 錯誤導致資料庫無法啟動(ORA-01565)

ORA-01172、ORA-01151錯誤處理

ORA-00600 [2662]錯誤解決

ORA-01078 和 LRM-00109 報錯解決方案

ORA-00471 處理方法筆記