Информатика · объяснение и практика
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 оставляет транзакцию открытой; дальнейшее решение должно быть явным. Этот опыт выполняют в отдельной базе с собственными строками и проверяют состояние обеих таблиц. Он объясняет момент проверки, а не рекомендует отключать защиту или менять порядок операций в рабочей системе без анализа её правил.
От чтения к действию
А теперь — вы
Частые вопросы
Любой NO ACTION автоматически откладывается до COMMIT?
Нет. Для нашего опыта задан отложенный ключ и явная транзакция; действие и режим проверки нужно различать.
RESTRICT станет отложенным из-за DEFERRABLE INITIALLY DEFERRED?
Нет. При существующей дочерней ссылке действие RESTRICT препятствует удалению родителя сразу.
Источники и работа с материалом
Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.
Продолжить разбираться
UNIQUE и NULL: одинаковые пустые значения зависят от договора СУБД
Почему SQLite допускает несколько NULL в UNIQUE: неизвестное значение, пустая строка и отдельное требование NOT NULL.
Открыть темуCHECK и неизвестное значение: когда NULL не нарушает ограничение
CHECK и NULL в SQLite: почему условие score > 0 допускает пропуск и когда требуется отдельное NOT NULL.
Открыть тему