Информатика · объяснение и практика

NO ACTION и RESTRICT: когда проверяется ссылка в транзакции

Отложенный NO ACTION и немедленный RESTRICT в SQLite: две команды одной транзакции, проверка COMMIT и порядок перепривязки дочерней строки.

Синие фигурные скобки держат между собой оранжевый квадрат.

В теме Таблицы, базы данных и SQL · Учебная карта

В маршруте Запросы, ограничения и устойчивое чтение · 10 тем

Главная мысль

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

Задайте ограничение и начало транзакции

В собственной учебной базе p содержит родительские ключи 7 и 9, c — одну строку с pid=7. Ссылка объявлена как REFERENCES p(id) ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED. Внешние ключи включены и проверены для соединения до BEGIN. Обе таблицы созданы только для этого опыта.

Слова DEFERRABLE INITIALLY DEFERRED существенны: просто NO ACTION не обещает отсрочку до конца транзакции. В режиме отдельных автоматически фиксируемых команд также нет произвольного промежутка между удалением и последующим исправлением. Для сравнения нужны явно одинаковые условия, кроме выбранного действия связи.

Проверьте последовательность и конечные строки

После BEGIN выполняем DELETE FROM p WHERE id=7, затем UPDATE c SET pid=9 WHERE pid=7 и COMMIT. В промежутке ссылка указывает на уже удалённый ключ, но к фиксации ребёнок ссылается на существующую девятку. При заданном отложенном NO ACTION эта последовательность допустима.

Теперь в отдельной копии опыта меняем только действие на ON DELETE RESTRICT. Первая же команда удаления семёрки получает ошибку: зависимая строка ещё существует. Объявление DEFERRABLE не откладывает действие RESTRICT. Если сначала перепривязать c к девятке, а затем удалить семёрку, этого препятствия уже нет.

Пример

Отложенный NO ACTION: удалили p.id=7, но не выполнили UPDATE c и попытались COMMIT. Что произойдёт?

Фиксация нарушенной ссылки не допускается. Успех промежуточного DELETE не означает успех всей транзакции; нарушение нужно устранить до фиксации либо отменить транзакцию.

Отсрочка не означает отключение проверки

NO ACTION не создаёт отсутствующего родителя и не перепривязывает ребёнка самостоятельно. В нашем опыте допустимое итоговое состояние создаёт явный UPDATE. Если нового родителя девять тоже нет, смена pid на девять не исправит нарушение. Проверяется существование конечной ссылки, а не только факт второй команды.

При неудачном COMMIT из-за отложенного ограничения SQLite оставляет транзакцию открытой; дальнейшее решение должно быть явным. Этот опыт выполняют в отдельной базе с собственными строками и проверяют состояние обеих таблиц. Он объясняет момент проверки, а не рекомендует отключать защиту или менять порядок операций в рабочей системе без анализа её правил.

От чтения к действию

А теперь — вы

Внешние ключи включены. Есть родители 4 и 8, ребёнок ссылается на 4. В явной транзакции сначала удаляют 4, затем меняют ссылку на 8. Какое сравнение верно для двух иначе одинаковых отложенных ключей?

Частые вопросы

Любой NO ACTION автоматически откладывается до COMMIT?

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

RESTRICT станет отложенным из-за DEFERRABLE INITIALLY DEFERRED?

Нет. При существующей дочерней ссылке действие RESTRICT препятствует удалению родителя сразу.

Источники и работа с материалом

Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.

Продолжить разбираться