Oracle唯一約束中NULL的處理

根據NULL的定義，NULL表示的是未知，因此兩個NULL比較的結果既不相等，也不不等，結果仍然是未知。根據這個定義，多個NULL值的存在應該不違反唯一約束。

 

 

實際上Oracle也是如此實現的：

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> ALTER TABLE T ADD UNIQUE (ID);

表已更改。

SQL> INSERT INTO T VALUES (1);

已建立 1 行。

SQL> INSERT INTO T VALUES (1);
INSERT INTO T VALUES (1)
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (YANGTK.SYS_C007300)

SQL> INSERT INTO T VALUES (NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (NULL);

已建立 1 行。

但是當唯一約束為複合欄位時，則情況發生了變化。根據Oracle文件的描述，對於複合欄位的唯一約束，不為空欄位的值是不能重複的。也就是說，如果兩個欄位構成了一個唯一約束，其中一個欄位為空，那麼另一個欄位的值不能出現重複。

SQL> DROP TABLE T PURGE;

表已刪除。

SQL> CREATE TABLE T (ID NUMBER, ID2 NUMBER);

表已建立。

SQL> ALTER TABLE T ADD UNIQUE (ID, ID2);

表已更改。

SQL> INSERT INTO T VALUES (1, 1);

已建立 1 行。

SQL> INSERT INTO T VALUES (1, NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (2, NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (1, NULL);
INSERT INTO T VALUES (1, NULL)
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (YANGTK.SYS_C007301)

SQL> INSERT INTO T VALUES (NULL, NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (NULL, NULL);

已建立 1 行。

SQL> INSERT INTO T VALUES (NULL, NULL);

已建立 1 行。

對於全部為NULL的情況，仍然和單欄位唯一約束一樣，不會造成重複，但是對於部分為NULL的情況，就如上面例子所示，只要其中不為NULL的部分發生了重複，Oracle就認為約束髮生了重複。

而這似乎和NULL的定義有所衝突，第一次看concept的時候一直沒有搞明白Oracle為什麼這麼實現，不過這次再看concept的時候，已經想明白了。

由於Oracle的唯一約束是依賴索引實現的，而Oracle的BTREE索引又是不儲存NULL值的，所以鍵值全部為NULL的記錄不會記錄在索引中，因此也就不會違反唯一約束了，而對於部分為NULL的記錄，索引是要記錄數值的，因此一旦鍵值中非NULL部分發生了衝突，Oracle就認為違反了的唯一約束。

Oracle在這裡還是選擇了自己的方便的方法來實現，而沒有完全真正的根據NULL的定義去實現唯一約束。