Oracle唯一約束與NULL值語義深度解析：單列vs複合索引的差異根因

核心現象：NULL在唯一約束中的「雙重標準」

-- 單列唯一約束：多個NULL共存 ✓
CREATE TABLE t1 (id NUMBER);
ALTER TABLE t1 ADD UNIQUE (id);
INSERT INTO t1 VALUES (NULL);  -- 成功
INSERT INTO t1 VALUES (NULL);  -- 成功
INSERT INTO t1 VALUES (NULL);  -- 成功

-- 複合唯一約束：部分NULL視為衝突 ✗
CREATE TABLE t2 (id1 NUMBER, id2 NUMBER);
ALTER TABLE t2 ADD UNIQUE (id1, id2);
INSERT INTO t2 VALUES (1, NULL);  -- 成功
INSERT INTO t2 VALUES (1, NULL);  -- ORA-00001: 違反唯一約束！
INSERT INTO t2 VALUES (NULL, NULL); -- 成功
INSERT INTO t2 VALUES (NULL, NULL); -- 成功

根因深度剖析：B-Tree索引的NULL存儲邏輯

Oracle B-Tree索引核心特征

• 完全NULL鍵值不入索引： 索引條目中所有列均為NULL時，不生成索引條目；
• 部分NULL鍵值正常入索引： 至少一列非NULL時，生成索引條目，NULL列以特殊標記存儲；
• 唯一約束實現機制： 依賴唯一索引強制執行，索引衝突即約束違反。

語義映射表

SQL標準 vs Oracle實現的哲學衝突

SQL:1999標準 (UNIQUE約束)：** 「若唯一約束定義在多列上，則任意兩行在所有約束列上的值不得全相等。NULL值不視為相等。」

→ 即：(1, NULL) 和 (1, NULL) 在標準下**不違反**唯一約束，因為NULL≠NULL。

Oracle選擇了「索引實現便利性」優於「標準語義純粹性」：利用現有B-Tree索引邏輯直接強制約束，避免額外的「全鍵值比較邏輯」開發成本。

實戰影響與應對策略

場景一：業務主鍵包含可選列(如email可為空)

-- 錯誤設計：複合唯一約束導致誤判衝突
CREATE TABLE users (user_id NUMBER, email VARCHAR2(100), phone VARCHAR2(20));
ALTER TABLE users ADD UNIQUE (email, phone);  -- 同email不同phone或同phone不同email均衝突！

-- 正確設計方案：
-- 方案A：單列唯一約束 + 條件唯一索引 (11g+)
CREATE UNIQUE INDEX uq_users_email ON users (email) WHERE email IS NOT NULL;
CREATE UNIQUE INDEX uq_users_phone ON users (phone) WHERE phone IS NOT NULL;

-- 方案B：虛擬列轉換NULL為特殊標記
ALTER TABLE users ADD (email_nvl VARCHAR2(101) GENERATED ALWAYS AS (NVL(email, '#NULL#')));
CREATE UNIQUE INDEX uq_users_email ON users (email_nvl);

場景二：數據遷移/同步時的約束衝突

從PostgreSQL/MySQL(遵循標準語義)遷移至Oracle時，複合唯一鍵包含NULL的數據可能在Oracle報ORA-00001。

-- 遷移前檢查腳本
SELECT col1, col2, COUNT(*)
FROM source_table
WHERE col1 IS NOT NULL OR col2 IS NOT NULL  -- 排除全NULL行
GROUP BY col1, col2
HAVING COUNT(*) > 1;  -- 這些數據在Oracle會衝突

版本演進與替代方案

結語

Oracle唯一約束對NULL的處理是「工程妥協產物」：單列/全NULL遵循標準，部分NULL違背標準但符合索引實現邏輯。DBA/開發者必須明確知曉這一差異，在設計複合唯一鍵時優先使用條件唯一索引(12c+)或虛擬列方案(11g+)實現標準語義，避免業務邏輯與數據庫約束語義不一致導致的數據質量事故。