sql - 具有空值的复合唯一约束

Question

如何在 SQLite 中设置可以正确处理空值的复合唯一约束？这可能通过示例更好地表达:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    suffix TEXT,
    UNIQUE (first_name, last_name, suffix)
);

当多次输入带有后缀的人时，约束会按预期工作。

-- Last insert is denied
INSERT INTO people (first_name, last_name, suffix)
VALUES
    ('Joe', 'Dirt', 'Sr'),
    ('Joe', 'Dirt', 'Jr'),
    ('Joe', 'Dirt', 'Sr');

但是，当多次插入没有后缀的人时，唯一性约束不适用。

-- Both are permitted
INSERT INTO people (first_name, last_name)
VALUES
    ('Chris', 'Farley'),
    ('Chris', 'Farley');

我知道 SQLite 将 null 值视为相互独立的，但有没有办法解决这个问题？

Answer 1

您可以创建一个独特的 partial index first_name 和 last_name 仅当后缀为 null 时:

CREATE UNIQUE INDEX idx_people ON people(first_name, last_name) 
WHERE suffix IS NULL;

查看简化的 demo .

如果您的 SQLite 版本是 3.31.0+，您可以创建一个生成的列，当 suffix 为 null 时返回一个空字符串，并在唯一约束中使用它而不是 后缀:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    suffix TEXT,
    str_suffix GENERATED ALWAYS AS (COALESCE(suffix, '')),
    UNIQUE (first_name, last_name, str_suffix)
);