SQL Server 如何確保可為 NULL 的欄位中非 NULL 值唯一

問題描述

表中某欄位允許 NULL，NULL 可重複，但非 NULL 值必須唯一。例如：

CREATE TABLE test_tb (
    TestId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Caption NVARCHAR(100) NULL
);

方案一：唯一索引 ❌ 失效

CREATE UNIQUE NONCLUSTERED INDEX un_test_tb ON test_tb(Caption);

測試插入兩個 NULL → 報錯 2601，SQL Server 視 NULL 為重複鍵值，不符合需求。

方案二：CHECK 約束 + 純量函數 ✅ 可行但繁瑣

建立驗證函數

CREATE FUNCTION dbo.fn_CK_test_tb_Caption()
RETURNS BIT
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM test_tb a
        WHERE a.Caption IS NOT NULL
          AND EXISTS (
              SELECT 1 FROM test_tb
              WHERE Caption = a.Caption AND TestId <> a.TestId
          )
    ) RETURN 0;
    RETURN 1;
END;

加上 CHECK 約束

ALTER TABLE test_tb
ADD CONSTRAINT CK_test_tb_Caption CHECK (dbo.fn_CK_test_tb_Caption() = 1);

測試：NULL 可多筆，'AAA'、'BBB' 正常，再插 'BBB' → 報錯。邏輯正確，但效能差、維護成本高。

方案三：SQL Server 2008+ 篩選索引 ✅ 優雅且高效

CREATE UNIQUE NONCLUSTERED INDEX un_test_tb
ON test_tb(Caption)
WHERE Caption IS NOT NULL;

同樣測試通過，僅索引非 NULL 列，完美滿足需求。

版本相容性對照

拋磚引玉

有無「兼容舊版本又優雅」的寫法？歡迎留言分享（如 INSTEAD OF INSERT/UPDATE 觸發器手工檢查、計算欄位 + 唯一索引等變通做法）。