UNIQUE и CHECK

Первичный ключ гарантирует, что у двух строк не будет одинакового идентификатора. Часто уникальным должен быть и другой столбец: у двух клиентов не может быть одинаковой почты, иначе магазин не поймёт, чей это аккаунт. Это и означает UNIQUE после типа столбца. Так магазин мог бы хранить игроков своих турниров:

CREATE TABLE players (
    id INTEGER PRIMARY KEY,
    nickname TEXT NOT NULL UNIQUE,
    age INTEGER NOT NULL CHECK (age >= 13)
) STRICT;

Второй игрок с уже занятым ником отклоняется. Ни одна из двух строк не вставляется: инструкция, завершившаяся ошибкой, ничего не меняет.

INSERT INTO players (nickname, age)
VALUES ('NightOwl', 15),
       ('NightOwl', 22);

Error on line 1: UNIQUE constraint failed: players.nickname

UNIQUE допускает NULL, причём сколько угодно раз: NULL означает «нет значения», а одно отсутствующее значение не равно другому. Если в столбце всегда должно быть значение, добавьте NOT NULL, как выше. Адрес электронной почты клиентов магазина тоже объявлен как TEXT NOT NULL UNIQUE.

CHECK (после него в скобках идёт условие) задаёт правило, которому должна соответствовать каждая строка. Условие записывается так же, как в WHERE, а турниры выше — для игроков от 13 лет:

INSERT INTO players (nickname, age)
VALUES ('TinyTim', 11);

Error on line 1: CHECK constraint failed: age >= 13

В таблицах магазина CHECK тоже используется: количество звёзд в отзыве должно быть BETWEEN 1 AND 5, количество товара — > 0, а статус заказа — IN ('paid', 'refunded'). CHECK, который сравнивает два столбца, или UNIQUE сразу для двух столбцов пишется после последнего столбца, на отдельной строке:

CREATE TABLE tournaments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    starts TEXT NOT NULL,
    ends TEXT NOT NULL,
    CHECK (ends >= starts),
    UNIQUE (name, starts)
) STRICT;

UNIQUE (name, starts) допускает два турнира с одним названием и два турнира, которые начинаются в один день, но не два турнира с одним названием в один день.

Типичная ошибка: забыть, что CHECK пропускает NULL. Если в age стоит NULL, условие age >= 13 не истинно и не ложно, а CHECK отклоняет строку, только когда условие ложно. Поэтому столбцу с CHECK обычно нужен и NOT NULL.