Составное уникальное ограничение sqlite3 с нулевыми значениями

avatar
7rhvnn
1 июля 2021 в 19:18
76
2
1

Как установить составное уникальное ограничение, которое может правильно обрабатывать нулевые значения в 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 рассматривает нулевые значения как независимые друг от друга, но есть ли правильный способ обойти это?

Будем очень признательны за любую помощь. Спасибо!

Источник

Ответы (2)

avatar
forpas
1 июля 2021 в 19:27
1

Вы можете создать уникальный partial index для first_name и last_name, только если суффикс равен нулю:

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

Смотреть упрощенную демонстрацию.

Если у вас версия SQLite 3.31.0+, вы можете создать сгенерированный столбец, который возвращает пустую строку, когда suffix равно null, и использовать его в уникальном ограничении вместо suffix:

. 
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)
);
avatar
dmg
1 июля 2021 в 19:30
1

Сделайте по умолчанию для суффикса пустую строку и установите ее как ненулевую. Это, вероятно, решит вашу проблему (NULL не совпадает с '', NULL - это я не знаю, поэтому в приведенном ниже коде вы говорите, что отчество является пустой строкой, а не NULL). 

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