CHECK и UNIQUE в PostgreSQL: что проверить до запуска

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

Что подготовить

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

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

Проверьте данные до изменения схемы

Сначала найдите строки, которые нарушат будущее правило. Для CHECK с условием price >= 0 проверьте отрицательные цены:

SELECT id, price
FROM products
WHERE price < 0;

Затем отдельно проверьте NULL, если значение обязательно. Условие CHECK считается нарушенным, когда оно даёт FALSE; результат NULL не считается нарушением. Поэтому CHECK (price >= 0) не запрещает отсутствующую цену.

SELECT id
FROM products
WHERE price IS NULL;

Если правило требует и неотрицательную, и обязательную цену, нужны оба ограничения: CHECK для диапазона и NOT NULL для заполненности. Не пытайтесь заменить ими друг друга.

Для UNIQUE найдите повторяющиеся значения. Например, если адрес электронной почты должен быть уникальным:

SELECT email, COUNT(*)
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

До создания ограничения разберите найденные строки: объедините дубли, исправьте значения или выберите другой ключ. Не удаляйте записи автоматически, пока не проверили связанные данные и требования приложения.

Добавьте ограничение с нужным смыслом

1. Ограничьте значение в строке. В PostgreSQL 16 можно создать CHECK для цены. Проверьте, что запрос создания проходит, а вставка отрицательной цены отклоняется.

ALTER TABLE products
ADD CONSTRAINT products_price_nonnegative
CHECK (price >= 0);

Проверка: после установки попробуйте на тестовой базе вставить строку с отрицательной ценой — PostgreSQL должен отклонить операцию с нарушением ограничения.

1. Запретите повторы. Если столбец не должен содержать повторяющиеся значения, задайте UNIQUE. Проверка: повторная вставка того же ненулевого email должна завершиться ошибкой уникальности.

ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE (email);

В PostgreSQL 16 обычный UNIQUE допускает несколько NULL: NULL не считается равным другому NULL. Если email должен быть заполнен, отдельно добавьте NOT NULL. В PostgreSQL 15 и новее доступно NULLS NOT DISTINCT, чтобы считать NULL повторяющимся значением; применяйте его только если это соответствует модели данных.

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

ALTER TABLE memberships
ADD CONSTRAINT memberships_user_group_unique
UNIQUE (user_id, group_id);

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

1. Ограничьте только часть строк. Если уникальность нужна лишь для активных записей, обычное ограничение не выразит условие по части строк. Используйте частичный уникальный индекс. Проверка: две активные записи с одинаковым ключом отклоняются, а архивная запись с таким ключом допускается.

CREATE UNIQUE INDEX users_active_email_unique
ON users (email)
WHERE active;

Частичный индекс не является ограничением UNIQUE, добавленным через ALTER TABLE. В частности, для такого индекса нельзя использовать режим отложенной проверки ограничения.

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

Где правило ломается

CHECK не проверяет другие строки

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

UNIQUE может не соответствовать бизнес-правилу

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

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

Выбор коротко

Ограничение в базе — последняя линия защиты от неверных данных, а не замена проверке входа. Сначала сформулируйте правило так, чтобы оно оставалось верным и при двух одновременных запросах.

Термин: отложенное ограничение

Отложенная проверка — проверка ограничения не после каждой команды, а к моменту завершения транзакции. В PostgreSQL UNIQUE может быть объявлен как DEFERRABLE; это полезно, например, когда транзакция временно создаёт конфликт, который устраняется до COMMIT. По умолчанию ограничения проверяются немедленно, а частичный уникальный индекс нельзя сделать отложенным.

Что делать дальше

На копии базы выполните четыре проверки: найдите нарушающие CHECK строки, NULL и дубли; примените миграцию; проверьте отклонение неверной записи; протестируйте конкурентную запись с одинаковым ключом. После развёртывания следите за ошибками ограничений и временем миграции. Если правило касается нескольких строк, сначала отдельно спроектируйте транзакционную защиту — CHECK здесь не решит задачу.