RESTRICT и NO ACTION в PostgreSQL: разница и практическое применение

Быстрый ответ

В SQL операторы RESTRICT и NO ACTION применяются для обеспечения целостности данных с использованием внешних ключей при удалении или обновлении связанных записей. Отличие между ними заключается в моменте активации проверки ограничений:

• NO ACTION: Ограничение проверяется в конце транзакции. Если выявляется нарушение связи, транзакция откатывается.
• RESTRICT: В большинстве систем управления базами данных (СУБД) работает аналогично NO ACTION, моментально блокируя операцию, способную нарушить связь внешнего ключа.

Важно учесть, что несмотря на внешнее сходство, специфика работы этих директив в каждой конкретной СУБД может отличаться.

Подробное понимание отложенных (DEFERRABLE) ограничений

В PostgreSQL разница между NO ACTION и RESTRICT особенно значительна благодаря возможности использования отложенных ограничений DEFERRABLE. Такие ограничения позволяют временное нарушение связей внешних ключей в рамках транзакции, при условии, что к её завершению все нарушения будут устранены.

NO ACTION в сочетании с отложенными (DEFERRABLE) ограничениями

• NO ACTION, сочетанный с DEFERRABLE, дает гибкость в управлении транзакциями. С его помощью, например, можно в рамках одной транзакции удалить родительскую запись и сразу вставить её обратно с измененными данными, минуя временные нарушения целостности.

BEGIN;
DELETE FROM parents WHERE id = 1; 
INSERT INTO parents(id, name) VALUES(1, 'NameChanged'); 
COMMIT;

Подробнее об этом расскажет наш спикер на видео

RESTRICT и отложенные (DEFERRABLE) ограничения

• Оператор RESTRICT, в отличие от NO ACTION, не совместим с отложенной проверкой. Он мгновенно прерывает любую попытку нарушить целостность данных.

BEGIN;
DELETE FROM children WHERE parent_id = 1;
ROLLBACK;

Режимы отложенных (DEFERRABLE) ограничений: INITIALLY DEFERRED и INITIALLY IMMEDIATE

• Для отложенных ограничений можно определить момент проверки: в конце транзакции (INITIALLY DEFERRED) или после каждой операции (INITIALLY IMMEDIATE).

Применение SET CONSTRAINTS

• Команда SET CONSTRAINTS позволяет контролировать момент проверки отложенных ограничений, адаптируя их к потребностям транзакции.

  Рекомендации по использованию в зависимости от ситуации

Выбор между NO ACTION и RESTRICT следует делать, исходя из контекста и требований, предъявляемых к операциям в вашей базе данных:

При сложной структуре транзакций

• Применяйте NO ACTION с отложенными ограничениями, если транзакция реализует сложную логику с возможностью временных нарушений целостности внешних ключей.

Для непрерывного обеспечения целостности данных

• Оператор RESTRICT, обеспечивающий сохранение целостности данных на каждом этапе, блокирует любые операции, связанные с нарушением целостности внешних ключей.

Зависимость от логики приложения

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

Критерии практического использования

• В зависимости от требований к дизайну базы данных, "RESTRICT" обеспечит более жёсткий контроль над данными, в то время как комбинация NO ACTION и DEFERRABLE даст возможность гибкого управления транзакциями.

Визуализация

Сравнение двух полей, разделённых запирающимся забором:

RESTRICT:         NO ACTION:
Попытка перейти   Попытка перейти
в соседнее поле   в соседнее поле

🌾 🔒 🚫           🌾 🔒 ⏳
(Сигнал тревоги)  (Подождем немного)

RESTRICT отреагирует на попытку пересечения, в то время как NO ACTION будет ожидать фактической попытки пройти через.

Как сделать выбор?

Учитывая выбор между NO ACTION и RESTRICT, рекомендуется тщательно продумать архитектуру вашей базы данных и особенности реализации этих операторов:

Уровень изоляции транзакций: Read Committed и Serializable

• Выбор уровня изоляции транзакций может оказаться ключевым при решении о применении NO ACTION в сочетании с отложенными ограничениями.

Почему стоит использовать отложенные (DEFERRABLE)

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

Оценка влияния на архитектуру

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

Полезные материалы

1. ISO/IEC 9075-1:2016 – международный стандарт, определяющий основы языка SQL.
2. PostgreSQL: Документация об ограничениях – подробности про ограничения в PostgreSQL, включая внешние ключи.
3. MySQL: Ограничения FOREIGN KEY – руководство по использованию ограничений внешнего ключа в MySQL.
4. SQLite и внешние ключи – описание поддержки ограничений внешних ключей в SQLite.
5. Oracle: Документация об ограничениях – обзор ограничений в базе данных Oracle.
6. Документация IBM для DB2 – информационный ресурс о процессе установки ограничений в базе данных DB2.
7. W3Schools: SQL FOREIGN KEY Constraint – урок по ограничениям внешнего ключа в SQL.
8. Tutorialspoint: SQL – Внешний ключ – основные принципы работы с внешними ключами на SQL с примерами.