Oracle 唯一約束與 NULL 值的深層邏輯：單欄位 vs 複合鍵的差異解析

核心結論

• 單欄位唯一約束：允許任意多筆 NULL，因 NULL = NULL 為 UNKNOWN，不違反唯一性。
• 複合唯一約束：非 NULL 欄位值的組合必須唯一。只要非 NULL 部分重複，即判定違反，即使其他欄位為 NULL。

實測驗證

單欄位：多 NULL 共存

CREATE TABLE t (id NUMBER);
ALTER TABLE t ADD UNIQUE (id);

INSERT INTO t VALUES (1);      -- 成功
INSERT INTO t VALUES (1);      -- ORA-00001
INSERT INTO t VALUES (NULL);   -- 成功
INSERT INTO t VALUES (NULL);   -- 成功
INSERT INTO t VALUES (NULL);   -- 成功

複合鍵 (ID, ID2)：部分 NULL 衝突

DROP TABLE t PURGE;
CREATE TABLE t (id NUMBER, id2 NUMBER);
ALTER TABLE t ADD UNIQUE (id, id2);

INSERT INTO t VALUES (1, 1);      -- 成功
INSERT INTO t VALUES (1, NULL);   -- 成功
INSERT INTO t VALUES (2, NULL);   -- 成功
INSERT INTO t VALUES (1, NULL);   -- ORA-00001  (ID=1 重複)
INSERT INTO t VALUES (NULL, NULL);-- 成功
INSERT INTO t VALUES (NULL, NULL);-- 成功
INSERT INTO t VALUES (NULL, NULL);-- 成功

底層機制：B-Tree 索引不存 NULL

Oracle 唯一約束由 唯一 B-Tree 索引 執行。B-Tree 索引**不儲存完全為 NULL 的鍵值** (所有欄位皆 NULL)。

• 單欄位：NULL 不進索引 → 索引無重複 → 允許無限 NULL。
• 複合鍵 (A, B)：
  • (1, NULL) → 索引鍵為 (1) (忽略尾部 NULL) → 存入索引。
  • 第二筆 (1, NULL) → 索引鍵同為 (1) → 唯一衝突。
  • (NULL, NULL) → 全 NULL → 不進索引 → 允許無限筆。

為什麼 Oracle 這樣設計？

1. 效能優先：利用現有 B-Tree 索引機制，無需額外邏輯判斷 NULL 語意。
2. SQL 標準妥協：SQL:1992 允許唯一約束對 NULL 寬鬆；SQL:1999 引入 UNIQUE NULLS NOT DISTINCT 選項，Oracle 至今未實作 (19c 仍無)。
3. 相容性：變更行為會破壞既有應用邏輯。

實務影響與繞過方案

場景：業務要求「單據號 + 明細行號」唯一，但明細行號可為 NULL (表頭單據)

直接建立 UNIQUE (doc_no, line_no) 會導致多筆表頭 (line_no=NULL) 衝突。

方案 1：函數式索引 (Function-Based Index) —— 推薦

-- 將 NULL 轉為特殊值 -1 參與唯一性判斷
CREATE UNIQUE INDEX uq_doc_line ON documents (
  doc_no,
  NVL(line_no, -1)
);

優點：語意清晰、索引可被查詢使用、不修改資料。

方案 2：虛擬欄位 + 唯一約束 (11g+)

ALTER TABLE documents ADD (line_no_nvl NUMBER GENERATED ALWAYS AS (NVL(line_no, -1)) VIRTUAL);
ALTER TABLE documents ADD CONSTRAINT uq_doc_line UNIQUE (doc_no, line_no_nvl);

方案 3：觸發器檢查 (效能差、易死鎖，不建議)

總結

設計複合唯一約束時，**務必評估「部分 NULL」業務語意**，必要時用函數式索引或虛擬欄位修正行為。