В чем проблема каскада внешних ключей с несколькими путями и циклами?
В MSSQL 2005 я просто ударил печально известное сообщение об ошибке:
Введение ограничения XXX FOREIGN KEY в таблицу YYY может привести к появлению циклов или нескольких каскадных путей. Укажите ON DELETE NO ACTION или ON UPDATE NO ACTION, либо измените другие ограничения FOREIGN KEY.
Теперь в Stackru есть несколько тем об этом сообщении об ошибке, поэтому у меня уже есть решение (в моем случае мне придется использовать триггеры), но мне любопытно, почему вообще существует такая проблема.
Насколько я понимаю, есть два сценария, которых они хотят избежать - цикл и несколько путей. Цикл был бы, где две таблицы имеют каскадные внешние ключи друг к другу. ОК, цикл также может охватывать несколько таблиц, но это основной случай, который будет легче анализировать.
Множественные пути могут быть, когда TableA имеет внешние ключи для TableB и TableC, а TableB также имеет внешний ключ для TableC. Опять же - это минимальный базовый случай.
Я не вижу никаких проблем, которые могут возникнуть, когда запись будет удалена или обновлена в любой из этих таблиц. Конечно, вам, возможно, придется запрашивать одну и ту же таблицу несколько раз, чтобы увидеть, какие записи нужно обновить / удалить, но действительно ли это проблема? Это проблема с производительностью?
В других темах SO люди даже отмечают использование каскадов как " рискованные" и заявляют, что " решение каскадных путей является сложной проблемой". Зачем? Где риск? В чем проблема?
6 ответов
У вас есть дочерняя таблица с 2 каскадными путями от одного и того же родителя: один "удалить", один "ноль".
Что имеет приоритет? Что вы ожидаете после этого? так далее
Примечание. Триггер - это код, который может добавить некоторый интеллект или условия к каскаду.
Причина, по которой мы запрещаем использование каскадного удаления, связана с производительностью и блокировками. Да, это не так плохо, когда вы удаляете одну запись, но рано или поздно вам придется удалить большую группу записей, и ваша база данных остановится.
Если вы удаляете достаточно записей, SQL Server может перерасти в блокировку таблицы, и никто не сможет ничего сделать с таблицей, пока она не будет завершена.
Недавно мы перенесли одного из наших клиентов на его собственный сервер. В рамках сделки нам также пришлось удалить все записи этого клиента с нашего исходного сервера. Удаление всей его информации в пакетном режиме (чтобы не вызывать проблем у других пользователей) заняло пару месяцев. Если бы мы настроили каскадное удаление, база данных была бы недоступна для других клиентов в течение длительного времени, так как миллионы записей были удалены за одну транзакцию, и сотни таблиц были заблокированы, пока транзакция не была выполнена.
Я также мог бы увидеть сценарий, в котором могла бы возникнуть тупик при использовании каскадного удаления, потому что мы не контролируем порядок, в котором должен был бы идти каскадный путь, и наша база данных несколько денормализована, и в большинстве таблиц появляется клиентская информация. Поэтому, если он заблокировал одну таблицу с внешним ключом и для третьей таблицы, а также для таблицы клиентов, которая находилась по другому пути, он, возможно, не смог бы проверить эту таблицу для удаления из третьей таблицы, поскольку это все одна транзакция и блокировки не будут сняты, пока это не будет сделано. Поэтому, возможно, он не позволил бы нам устанавливать каскадные удаления, если бы видел возможность создания тупиковых ситуаций в транзакции.
Другая причина, по которой следует избегать каскадного удаления, заключается в том, что иногда наличие дочерней записи является достаточной причиной, чтобы не удалять родительскую запись. Например, если у вас есть таблица клиентов, и у этого клиента были заказы в прошлом, вы не захотели бы удалить его и потерять информацию о фактическом заказе.
Рассмотрим таблицу сотрудников:
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 CASCADE", а другой "ON DELETE RESTRICT", или "NO ACTION", результат (результат) непредсказуем. Это зависит от того, какой триггер удаления (это также триггер, но который вам не нужно создавать самостоятельно) будет выполнен первым.
Я согласен с тем, что каскады являются "рискованными" и их следует избегать. (Лично я предпочитаю каскадное изменение изменений, а не то, чтобы сервер SQL автоматически позаботился о них). Это также потому, что даже если сервер sql удалил миллионы строк, выходные данные все равно будут отображаться как
(Затронут 1 ряд)
Я думаю, использовать опцию ON DELETE CASCADE или нет - это вопрос бизнес-модели, которую вы реализуете. Отношения между двумя бизнес-объектами могут быть либо простой "ассоциацией", где оба конца отношения связаны, но в остальном независимыми объектами, жизненный цикл которых отличается и управляется другой логикой. Однако существуют также отношения "агрегации", в которых один объект может фактически рассматриваться как "родитель" или "владелец" "дочернего" или "подробного" объекта. Существует еще более сильное понятие "композиционных" отношений, когда объект существует только как композиция из нескольких частей. В случае "ассоциации" вы обычно не объявляете ограничение ON DELETE CASCADE. Однако для агрегатов или композиций ON DELETE CASCADE помогает вам точнее и декларативнее отобразить вашу бизнес-модель в базе данных. Вот почему меня раздражает, что MS SQL Server ограничивает использование этой опции одним каскадным путем. Если я не ошибаюсь, многие другие широко используемые системы баз данных SQL не накладывают таких ограничений.