Какие ограничения (constraints) бывают в PostgreSQL

Что проверяют

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

Как отвечать

Пройдите по типам с нюансом к каждому:

• NOT NULL: простейшее ограничение, проверяется дешевле остальных;
• CHECK: условие по значениям одной строки, ссылаться на другие таблицы и подзапросы нельзя. Условие с NULL даёт UNKNOWN, и строка проходит: CHECK (price > 0) пустое значение не отсечёт;
• UNIQUE: автоматически создаёт индекс. По умолчанию NULL считаются различными, поэтому несколько пустых значений в колонке уживаются; с PostgreSQL 15 это переключается через UNIQUE NULLS NOT DISTINCT;
• PRIMARY KEY: UNIQUE плюс NOT NULL, ровно один на таблицу;
• FOREIGN KEY: ссылочная целостность плюс ON DELETE и ON UPDATE: NO ACTION по умолчанию, RESTRICT, CASCADE, SET NULL, SET DEFAULT. Индекс на ссылающейся колонке база не создаёт сама, а без него удаление родителя даёт полный проход по дочерней таблице;
• EXCLUDE: обобщение уникальности через операторы. Классика это запрет пересечения броней: EXCLUDE USING gist (room_id WITH =, period WITH &&), для скаляра нужен btree_gist.

Дальше эксплуатация. Отложить проверку до конца транзакции можно у UNIQUE, PRIMARY KEY, FOREIGN KEY и EXCLUDE через DEFERRABLE INITIALLY DEFERRED, у CHECK и NOT NULL нельзя. На большой таблице ограничение добавляют в два шага: ADD CONSTRAINT ... NOT VALID мгновенно и только для новых строк, потом VALIDATE CONSTRAINT без тяжёлой блокировки.

Где спотыкаются

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

Ждут, что CHECK отсечёт NULL. Условие даёт UNKNOWN, строка сохраняется, и пустые значения расползаются по таблице.

Добавляют FOREIGN KEY или CHECK одним ALTER TABLE в час пик. Проверка всех существующих строк идёт под блокировкой, запись встаёт, и на проде это выглядит как отказ сервиса.