Декларативные ограничения PostgreSQL: CHECK, UNIQUE и внешние ключи с ON DELETE

Что это такое

Документация ограничений PostgreSQL описывает CHECK — логическое выражение, вычисляемое для каждой строки и выполняющееся, если результат истинен или равен null, — NOT NULL, UNIQUE, считающее NULL различными, если не указано NULLS NOT DISTINCT, PRIMARY KEY, EXCLUDE и FOREIGN KEY. Ссылочное действие внешнего ключа определяет, что происходит при удалении строки, на которую ссылаются. Вариант по умолчанию NO ACTION вызывает ошибку, если ссылающиеся строки остаются на момент проверки ограничения; проверка может быть отложена до более позднего момента транзакции. RESTRICT запрещает удаление и не допускает отсрочки; CASCADE удаляет ссылающиеся строки; SET NULL и SET DEFAULT меняют значения ссылающихся столбцов. Документация указывает, что объявление внешнего ключа не создаёт автоматически индекс на ссылающихся столбцах.

Почему это важно

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

Как применять

• Задавайте NOT NULL для каждого столбца, если только «неизвестно» не имеет смысла, который приложение обрабатывает; добавляйте CHECK для неизменных правил (price >= 0, starts_at < ends_at).
• Выбирайте ON DELETE для каждой связи: CASCADE для принадлежащих родителю дочерних сущностей, например позиций заказа; RESTRICT или NO ACTION для ссылок, которые должен разрешить человек, например товара, на который ещё ссылаются заказы; SET NULL для необязательных связей.
• Индексируйте ссылающийся столбец каждого внешнего ключа, используемого в соединениях или имеющего удаляемые родительские строки; без индекса удаление родителя просматривает дочернюю таблицу.
• Давайте ограничениям имена (CONSTRAINT orders_total_nonneg CHECK (...)), чтобы сообщения об ошибках и миграции были понятными.
• На больших таблицах добавляйте ограничения внешнего ключа, CHECK и not-null как NOT VALID — документация ALTER TABLE разрешает эту опцию только для этих видов, — а затем выполняйте VALIDATE CONSTRAINT. Так существующие строки проверяются без удержания самой строгой блокировки на всё время просмотра.
• Используйте DEFERRABLE INITIALLY DEFERRED только для ограничений, которые необходимо временно нарушить внутри транзакции, например при циклических ссылках.

Подводные камни

CHECK не может обращаться к другим строкам или таблицам; для этого используйте ограничения EXCLUDE или триггеры. CASCADE на неверно выбранной связи незаметно удаляет гораздо больше данных, чем предполагалось. Ограничение UNIQUE на допускающем NULL столбце разрешает несколько NULL, если не указано NULLS NOT DISTINCT. Проверки, выполняемые лишь в коде приложения, обходятся всеми другими клиентами.