如何让SQLite在UNIQUE约束中视NULL为非不同值？

SQLite默认将UNIQUE约束中的NULL值视为不同值，若要让特定约束下的NULL被识别为重复值，可通过以下两种方案实现：

方法1：使用生成列（Generated Column） #

通过生成列将NULL替换为固定占位符，再对包含生成列的组合添加UNIQUE约束，让原字段为NULL时统一匹配占位符，触发重复约束。

CREATE TABLE Foo (
    Name TEXT NOT NULL,
    Nullable TEXT,
    -- 用空字符串作为NULL的占位符，可根据业务替换为其他无冲突值
    CoalescedNullable TEXT GENERATED ALWAYS AS (IFNULL(Nullable, '')) STORED,
    UNIQUE(Name, CoalescedNullable)
);

测试效果：

INSERT INTO Foo(Name, Nullable) VALUES('foo', 'bar');
INSERT INTO Foo(Name, Nullable) VALUES('foo', 'bar'); -- 报错：UNIQUE约束冲突（符合预期）

INSERT INTO Foo(Name) VALUES('baz');
INSERT INTO Foo(Name) VALUES('baz'); -- 报错：UNIQUE约束冲突（满足需求）

方法2：使用触发器（Trigger） #

创建插入和更新前的触发器，手动检查是否存在相同Name且Nullable为NULL的记录，存在则抛出错误。

CREATE TABLE Foo (
    Name TEXT NOT NULL,
    Nullable TEXT,
    UNIQUE(Name, Nullable)
);

-- 插入前检查重复的NULL记录
CREATE TRIGGER Foo_Unique_Null_Insert_Check
BEFORE INSERT ON Foo
FOR EACH ROW
WHEN NEW.Nullable IS NULL
BEGIN
    SELECT RAISE(ABORT, 'UNIQUE constraint failed: Foo.Name, Foo.Nullable')
    WHERE EXISTS (
        SELECT 1 FROM Foo WHERE Name = NEW.Name AND Nullable IS NULL
    );
END;

-- 更新前检查重复的NULL记录（排除当前行）
CREATE TRIGGER Foo_Unique_Null_Update_Check
BEFORE UPDATE ON Foo
FOR EACH ROW
WHEN NEW.Nullable IS NULL
BEGIN
    SELECT RAISE(ABORT, 'UNIQUE constraint failed: Foo.Name, Foo.Nullable')
    WHERE EXISTS (
        SELECT 1 FROM Foo WHERE Name = NEW.Name AND Nullable IS NULL AND rowid != NEW.rowid
    );
END;

测试效果：

INSERT INTO Foo(Name) VALUES('baz');
INSERT INTO Foo(Name) VALUES('baz'); -- 触发触发器，抛出错误（满足需求）

内容的提问来源于stack exchange，提问作者ToxicGLaDOS