В чем проблема с каскадным внешним циклом и циклами внешнего ключа?

В MSSQL 2005 я просто поразил печально известное сообщение об ошибке:

Представление ограничения FOREIGN KEY XXX в таблице YYY может вызывать циклы или несколько каскадных путей. Укажите ON DELETE NO ACTION или ON UPDATE NO ACTION или измените другие ограничения FOREIGN KEY.

Теперь у StackOverflow есть несколько тем об этом сообщении об ошибке, поэтому у меня уже есть решение (в моем случае мне придется использовать триггеры), но мне любопытно, почему существует такая проблема вообще.

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

Несколько путей были бы, если TableA имеет внешние ключи для TableB и TableC, а TableB также имеет внешний ключ для TableC. Опять же – это минимальный основной случай.

Я не вижу никаких проблем, которые возникнут, когда запись будет удалена или обновлена ​​в любой из этих таблиц. Конечно, вам может потребоваться несколько раз запросить одну и ту же таблицу, чтобы увидеть, какие записи необходимо обновлять / удалять, но действительно ли это проблема? Это проблема производительности?

В других SO-темах люди доходят до того, что метки используют каскады как « рискованные » и заявляют, что « разрешение каскадных путей является сложной проблемой ». Зачем? Где риск? В чем проблема?

У вас есть дочерняя таблица с двумя каскадными путями от одного и того же родителя: один «delete», один «null».

Что имеет преимущество? Что вы ожидаете после этого? и т.д

Примечание. Триггер – это код и может добавить некоторый интеллект или условия для каскада.

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

Если вы удалите достаточное количество записей, SQL Server может перерасти в блокировку таблицы, и никто не сможет ничего сделать с таблицей до ее завершения.

Недавно мы перевели одного из наших клиентов на свой сервер. В рамках этой сделки нам также пришлось удалить все записи этого клиента из нашего исходного сервера. Удаление всей его информации партиями (чтобы не создавать проблем с другими пользователями) заняло пару месяцев. Если бы мы установили каскадное удаление, база данных была бы недоступна для других клиентов в течение длительного времени, так как миллионы записей были удалены в одной транзакции, а сотни таблиц были заблокированы до тех пор, пока транзакция не будет выполнена.

Я мог бы также увидеть сценарий, в котором может возникнуть взаимоблокировка при использовании каскадного удаления, потому что мы не контролируем порядок, который должен был бы выполнить каскадный путь, и наша база данных несколько денормализована с помощью clientid, появляющейся в большинстве таблиц. Поэтому, если он заблокировал одну таблицу, в которой был внешний ключ, также для третьей таблицы, а также для таблицы клиентов, которая находилась в пути differnt, возможно, не удалось проверить эту таблицу, чтобы удалить ее из третьей таблицы, потому что это все одна транзакция и блокировки не будут выпущены до тех пор, пока это не будет сделано. Поэтому, возможно, это не позволило бы нам создавать каскадные удаления, если бы увидела возможность создания взаимоблокировок в транзакции.

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

Рассмотрим таблицу сотрудников:

CREATE TABLE Employee ( EmpID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(40) NOT NULL, MgrID INTEGER NOT NULL REFERENCES Employee(EmpID) ON DELETE CASCADE ); INSERT INTO Employees( 1, "Bill", 1); INSERT INTO Employees( 23, "Steve", 1); INSERT INTO Employees(234212, "Helen", 23); 

Теперь предположим, что Билл уходит в отставку:

 DELETE FROM Employees WHERE Name = "Bill"; 

Ooooppps; всех просто уволили!

[ Мы можем обсудить, правильны ли детали синтаксиса; я думаю, концепция стоит. ]

Я думаю, проблема заключается в том, что когда вы делаете один путь «УДАЛИТЬ КАСКАД», а другой «ON DELETE RESTRICT» или «NO ACTION», результат (результат) является непредсказуемым. Это зависит от того, какой триггер удаления (это также триггер, но тот, который вам не нужно строить самостоятельно) будет выполнен первым.

Я согласен с тем, что каскады являются «рискованными», и их следует избегать. (Я лично предпочитаю каскадировать изменения вручную, а потому, что SQL Server автоматически их позаботится). Это также потому, что даже если sql-сервер удалил миллионы строк, результат все равно будет отображаться как

(Затронуты 1 ряд)

Я думаю, нужно ли использовать опцию ON DELETE CASCADE, это вопрос о бизнес-модели, которую вы реализуете. Связь между двумя бизнес-объектами может быть либо простой «ассоциацией», где оба конца отношения связаны, но в остальном независимые объекты, жизненный цикл которых различен и управляется другой логикой. Однако существуют также «агрегирующие» отношения, когда один объект может фактически рассматриваться как «родитель» или «владелец» объекта «ребенок» или «подробно». Существует еще более четкое понятие «композиции», где объект существует только как состав из нескольких частей. В случае «ассоциации» вы обычно не объявляете ограничение ON DELETE CASCADE. Однако для агрегатов или композиций ON DELETE CASCADE помогает вам сопоставлять вашу бизнес-модель в базе данных более точно и декларативно. Вот почему меня раздражает, что MS SQL Server ограничивает использование этой опции одним каскадным путем. Если я не ошибаюсь, многие другие широко используемые системы баз данных SQL не налагают таких ограничений.