Ограничения: правила, которые защищает база — Postg

Ограничения: правила, которые защищает сама база

Ограничение (constraint) — правило для данных, которое проверяет PostgreSQL. Если новая или изменённая строка нарушает правило, команда завершается ошибкой и неверные данные не сохраняются.

Проверка в приложении нужна для удобного сообщения пользователю. Ограничение в базе нужно для надёжности: данные могут прийти из служебного сценария, импорта или новой версии сервиса.

#Обязательное значение и проверка условия

NOT NULL запрещает отсутствие значения. CHECK проверяет условие для строки:

CREATE TABLE payments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    amount numeric(14, 2) NOT NULL CHECK (amount > 0),
    currency text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
    status text NOT NULL CHECK (status IN ('created', 'captured', 'failed'))
);

Здесь сумма должна быть положительной, валюта — состоять из трёх заглавных латинских букв, а статус — входить в разрешённый список.

Есть важная особенность: CHECK нарушен только при результате FALSE. Результат UNKNOWN, возникающий из-за NULL, проходит проверку. Поэтому обязательный столбец обычно требует и NOT NULL, и отдельного условия.

CHECK должен зависеть от текущей строки. Правило между несколькими строками лучше выражать через UNIQUE, внешний ключ, исключающее ограничение или транзакцию.

#Первичный ключ и уникальность

PRIMARY KEY — главный идентификатор строки. Он всегда уникален и не допускает NULL. У таблицы только один первичный ключ, но он может состоять из нескольких столбцов.

UNIQUE защищает другое бизнес-правило:

CREATE TABLE app_users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id bigint NOT NULL,
    email text NOT NULL,
    UNIQUE (org_id, email)
);

id определяет пользователя внутри базы, а пара org_id, email запрещает двух пользователей с одной почтой в одной организации.

Обычный UNIQUE допускает несколько NULL: неизвестные значения не считаются равными. Если бизнес-правило разрешает только один NULL, укажите это явно:

UNIQUE NULLS NOT DISTINCT (org_id, external_alias)

Не заменяйте NULL пустой строкой только ради уникальности. Пустая строка — известное текстовое значение, а NULL — отсутствие значения.

#Внешний ключ

FOREIGN KEY — внешний ключ. Он требует, чтобы ссылка из одной таблицы вела на существующую строку другой таблицы.

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES app_users(id)
);

Так нельзя создать заказ с несуществующим user_id. Если данные разделены по организациям, одной ссылки на пользователя недостаточно: нужна также проверка организации.

ALTER TABLE app_users
ADD CONSTRAINT app_users_id_org_key UNIQUE (id, org_id);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id bigint NOT NULL,
    user_id bigint NOT NULL,
    FOREIGN KEY (user_id, org_id) REFERENCES app_users(id, org_id)
);

Теперь заказ может ссылаться только на пользователя той же организации.

PostgreSQL не создаёт индекс на столбцах дочерней таблицы автоматически. Индекс часто нужен для соединений и для быстрой проверки зависимых строк при удалении родителя, но его состав выбирают по реальным запросам.

#Что делать при удалении связанной строки

Внешний ключ должен определить судьбу дочерних строк:

RESTRICT или NO ACTION запрещает удалить родителя, пока есть ссылки;
CASCADE удаляет дочерние строки вместе с родителем;
SET NULL очищает ссылку, оставляя дочернюю строку;
SET DEFAULT подставляет значение по умолчанию.

Выбор зависит от смысла. Позиции черновика можно удалить вместе с черновиком, а платёжную историю обычно нельзя стирать вместе с учётной записью.

#Проверка в конце транзакции

Иногда несколько строк нужно временно привести через промежуточное неверное состояние. Например, поменять местами позиции 1 и 2 при уникальности позиции.

Ограничение с DEFERRABLE можно отложить до COMMIT:

CREATE TABLE board_items (
    id bigint PRIMARY KEY,
    position integer NOT NULL,
    CONSTRAINT board_items_position_key
        UNIQUE (position) DEFERRABLE INITIALLY IMMEDIATE
);

BEGIN;
SET CONSTRAINTS board_items_position_key DEFERRED;
UPDATE board_items
SET position = CASE position WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE position IN (1, 2);
COMMIT;

DEFERRED означает «проверь в конце транзакции». Если к COMMIT позиции всё ещё повторяются, вся транзакция завершится ошибкой.

#Новое правило для большой таблицы

Добавление ограничения к заполненной таблице может потребовать чтения всех строк. Этот процесс можно разделить:

ALTER TABLE invoices
ADD CONSTRAINT invoices_amount_nonnegative
CHECK (amount >= 0) NOT VALID;

После добавления новые и изменяемые строки уже проверяются. Старые строки проверяются отдельной командой:

ALTER TABLE invoices
VALIDATE CONSTRAINT invoices_amount_nonnegative;

Так короткое изменение структуры отделено от долгого чтения данных. Нагрузку и блокировки этапа VALIDATE всё равно нужно измерять.

#Как запретить пересечение периодов

UNIQUE запрещает равные значения. Исключающее ограничение (EXCLUDE) может запретить другое отношение, например пересечение двух броней одной комнаты.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    room_id bigint NOT NULL,
    booked_during tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        booked_during WITH &&
    )
);

Для двух строк с одинаковой комнатой оператор && не должен вернуть истину, то есть их временные диапазоны не могут пересекаться.

#Проверяйте и правильные, и неправильные случаи

Полезный тест не только успешно добавляет корректную строку, но и пытается записать:

повтор уникальной почты;
отрицательную сумму;
ссылку на пользователя другой организации;
пересекающуюся бронь;
дочернюю строку с несуществующим родителем.

Программа может проверять код ошибки SQLSTATE, например 23505 для нарушения уникальности. Код стабильнее текста сообщения, который зависит от языка и версии.

#Практика

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

Подробнее: ограничения PostgreSQL, ALTER TABLE.