База данных postgresql хранит не просто набор строк, а модель реального мира, и у этой модели есть правила: у заказа должен быть покупатель, цена не бывает отрицательной, два билета не получают одно и то же место. Ограничения целостности (constraints) - это способ записать такие правила прямо в схему таблицы, чтобы сама СУБД отказывалась принимать данные, которые их нарушают. В этом уроке разберём шесть видов ограничений (NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, EXCLUDE), научимся управлять каскадами внешних ключей и поймём, зачем нужны отложенные (DEFERRABLE) проверки.
Как это работает
Главная идея урока такая: правило, защищённое на уровне БД, нельзя обойти. Приложение можно переписать, можно зайти руками через psql, можно подключить второй сервис на другом языке - но если в таблице стоит CHECK (amount > 0), отрицательная сумма туда не попадёт никогда. Проверка в коде приложения - это вежливая просьба, а ограничение в схеме - это закон. Поэтому ключевые инварианты данных держат именно в базе.
NOT NULL запрещает в столбце пустое значение. Это самое дешёвое ограничение: признак хранится прямо в описании столбца. CHECK задаёт логическое условие на одну строку - выражение должно давать истину или NULL (NULL считается прошедшим проверкой, это частая ловушка). UNIQUE требует, чтобы значения в столбце (или наборе столбцов) не повторялись. PRIMARY KEY - это фактически UNIQUE плюс NOT NULL, и в таблице он может быть только один; он объявляет, что именно эта колонка опознаёт строку.
FOREIGN KEY связывает две таблицы: значение в дочерней таблице обязано существовать в родительской. Это и есть ссылочная целостность. Поведение при удалении или изменении родительской строки настраивается: RESTRICT и NO ACTION запрещают операцию, если есть зависимые строки; CASCADE удаляет или меняет их следом; SET NULL и SET DEFAULT подставляют в дочерний столбец NULL или значение по умолчанию.
EXCLUDE - самое мощное и редко используемое ограничение. Оно похоже на UNIQUE, но сравнивает строки не только на равенство. Можно потребовать, чтобы никакие две строки не конфликтовали по заданным операторам. Классика - бронирование: запретить пересечение интервалов времени для одной переговорки. Работает поверх индекса GiST.
Отдельно про момент проверки. Обычно ограничение проверяется сразу в конце оператора. Но если объявить его DEFERRABLE INITIALLY DEFERRED, проверка откладывается до COMMIT. Это спасает в круговых ссылках и при массовых перестановках ключей внутри одной транзакции.
SQL и примеры
Базовый набор ограничений в одной таблице. Обратите внимание на современный GENERATED IDENTITY вместо устаревшего serial.
Код: Выделить всё
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
amount numeric(10,2) NOT NULL CHECK (amount > 0),
status text NOT NULL DEFAULT 'new'
CHECK (status IN ('new','paid','shipped','cancelled')),
CONSTRAINT email_lower CHECK (email = lower(email))
);
Посмотрим на демобазу "Авиаперевозки" (схема bookings). Внешние ключи там связывают перелёты с аэропортами. Запрос показывает, как ссылочная целостность отражается в данных - каждый рейс ссылается на существующие аэропорты:
Код: Выделить всё
SELECT f.flight_no,
dep.airport_name AS departure,
arr.airport_name AS arrival
FROM bookings.flights f
JOIN bookings.airports dep ON dep.airport_code = f.departure_airport
JOIN bookings.airports arr ON arr.airport_code = f.arrival_airport
WHERE f.flight_no = 'PG0405'
LIMIT 5;
Внешний ключ с каскадом. Допустим, заводим позиции заказа:
Код: Выделить всё
CREATE TABLE order_items (
order_id bigint NOT NULL
REFERENCES orders(order_id) ON DELETE CASCADE,
sku text NOT NULL,
qty int NOT NULL CHECK (qty > 0),
PRIMARY KEY (order_id, sku)
);
EXCLUDE против пересечения интервалов. Нужен btree_gist, чтобы сравнивать обычный int оператором равенства внутри GiST:
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_booking (
room int NOT NULL,
during tsrange NOT NULL,
EXCLUDE USING gist (room WITH =, during WITH &&)
);
UNIQUE и NULL. По умолчанию UNIQUE считает каждый NULL уникальным, поэтому пустых значений может быть много. В PostgreSQL 15 появилась опция, меняющая это поведение:
Код: Выделить всё
CREATE TABLE accounts (
inn text UNIQUE NULLS NOT DISTINCT
);
Добавление ограничения без долгой блокировки. На большой таблице проверять весь объём данных дорого. Двухфазный приём - сначала NOT VALID, потом отдельная валидация:
Код: Выделить всё
ALTER TABLE orders
ADD CONSTRAINT amount_pos CHECK (amount > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT amount_pos;
Частые грабли
- CHECK пропускает NULL. Условие CHECK (price > 0) истинно для NULL, потому что выражение даёт NULL, а это не FALSE. Хотите запретить пустоту - ставьте ещё и NOT NULL.
- UNIQUE и куча NULL. По стандарту много NULL в уникальном столбце - это норма. Если нужно одно пустое значение, в PG15 берите NULLS NOT DISTINCT.
- Внешний ключ без индекса на дочерней стороне. PostgreSQL индексирует только родительский столбец автоматически. При ON DELETE CASCADE удаление родителя будет делать seq scan по дочерней таблице - создавайте индекс на FK-колонке руками.
- NO ACTION против RESTRICT. NO ACTION можно отложить через DEFERRABLE, а RESTRICT срабатывает немедленно и отложить его нельзя. Это не синонимы.
- CHECK не умеет подзапросы. Внутри CHECK нельзя обращаться к другим строкам или таблицам - только к текущей строке. Межтабличные правила - это FOREIGN KEY, EXCLUDE или триггеры.
- EXCLUDE без btree_gist. Для оператора = на обычных типах (int, text) нужен btree_gist, иначе GiST не знает такого класса операторов.
- Откладывать можно не всё. DEFERRABLE поддерживают UNIQUE, PRIMARY KEY, FOREIGN KEY и EXCLUDE. NOT NULL и CHECK откладывать нельзя - они проверяются построчно.
- Создайте таблицу authors (author_id GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL).
- Создайте books с внешним ключом author_id REFERENCES authors ON DELETE SET NULL и ограничением CHECK (pages > 0).
- Вставьте автора и пару книг, затем удалите автора и проверьте SELECT - что стало с author_id в книгах.
- Добавьте к books ограничение UNIQUE (title) и попробуйте вставить дубль названия. Прочитайте текст ошибки.
- Объявьте отложенный FK: пересоздайте связь как DEFERRABLE INITIALLY DEFERRED и внутри одной транзакции вставьте книгу раньше автора, потом автора, потом COMMIT.
- Через btree_gist и EXCLUDE сделайте таблицу расписания аудиторий, запретите пересечение занятий в одной аудитории и проверьте конфликт.
- Добавьте CHECK NOT VALID на существующие данные и отдельно выполните VALIDATE CONSTRAINT.
- Почему ограничение в схеме надёжнее проверки в коде приложения, и какой инвариант вы бы точно вынесли в базу?
- Чем PRIMARY KEY отличается от UNIQUE, и почему PK в таблице один, а UNIQUE может быть несколько?
- Как поведут себя дочерние строки при ON DELETE CASCADE, SET NULL и RESTRICT?
- Почему CHECK (x > 0) не ловит NULL и как это исправить?
- В каких случаях нужно DEFERRABLE INITIALLY DEFERRED и какие ограничения нельзя откладывать?
- Зачем для EXCLUDE с оператором равенства на int подключают расширение btree_gist?
