При настройке внешних ключей в SQL Server, при каких обстоятельствах вы должны его каскадировать при удалении или обновлении, и каковы причины этого?
Это, вероятно, относится и к другим базам данных.
Я больше всего ищу для конкретных примеров каждого сценария, желательно от тех, кто успешно их использовал.
Резюме того, что я видел до сих пор:
Внешние ключи - лучший способ обеспечить ссылочную целостность базы данных. Избежать каскадов из-за магии - это как писать все в сборке, потому что вы не доверяете магии компиляторов.
Плохое неправильное использование внешних ключей, например создание их в обратном порядке.
Пример Хуана Мануэля - это канонический пример, если вы используете код, есть еще много шансов оставить ложные объекты DocumentItems в базе данных, которые придут и укусят вас.
Каскадные обновления полезны, например, когда у вас есть ссылки на данные чем-то, что может измениться, например, основным ключом таблицы пользователей является комбинация имени, последнего имени. Затем вы хотите, чтобы изменения в этой комбинации распространялись туда, где они ссылаются.
@Aidan. Эта ясность, о которой вы говорите, связана с высокой стоимостью, возможностью оставить ложные данные в вашей базе данных, что мало. Для меня обычно просто отсутствие знакомства с БД и невозможность найти, какие FK находятся на месте, прежде чем работать с БД, которые способствуют этому страху. Либо это, либо постоянное злоупотребление каскадом, используя его там, где объекты не были концептуально связаны, или где вам нужно сохранить историю.
Я никогда не использую каскадные удаления.
Если я хочу что-то удалить из базы данных, я хочу явно указать базе данных, что я хочу извлечь.
Конечно, это функция, доступная в базе данных, и могут быть случаи, когда их можно использовать, например, если у вас есть таблица "order" и таблица "orderItem", вы можете очистить элементы, когда вы удаляете заказ.
Мне нравится ясность, которую я получаю от выполнения этого кода (или хранимой процедуры), а не "волшебства".
По той же причине я тоже не поклонник триггеров.
Что-то замечает, что если вы удаляете "заказ", вы получите отчет "1 строка затронуты", даже если каскадное удаление удалило 50 "orderItem".
Я много работаю с каскадным удалением.
Хорошо, что кто бы ни работал с базой данных, никогда не оставит никаких нежелательных данных. Если зависимости растут, я просто изменяю ограничения на диаграмме в Management Studio, и мне не нужно настраивать sp или dataacces.
Тем не менее, у меня есть 1 проблема с каскадным удалением, и это круговые ссылки. Это часто приводит к частям базы данных, которые не имеют каскадных удалений.
Один пример - когда у вас есть зависимости между объектами... ie: Document → DocumentItems (при удалении документа, DocumentItems не имеют причины для существования)
Я делаю много работы с базами данных и редко нахожу каскадные удаления полезными. Когда-то я использовал их эффективно в базе данных отчетов, которая обновляется в ночной работе. Я уверен, что любые измененные данные импортируются правильно, удалив любые записи верхнего уровня, которые были изменены с момента последнего импорта, а затем reimport измененные записи и все, что с ними связано. Это избавляет меня от необходимости писать много сложных удалений, которые выглядят снизу вверх до верхней части моей базы данных.
Я не считаю, что каскадные удаления будут такими же плохими, как триггеры, поскольку они только удаляют данные, триггеры могут иметь всевозможные неприятные вещи внутри.
В общем, я вообще избегаю реальных удалений и использую логические удаления (т.е. имея бит столбца с именем isDeleted, который получает значение true).
Используйте каскадное удаление, где вы хотите, чтобы запись с FK была удалена, если удалялась его ссылка на PK-запись. Другими словами, если запись не имеет смысла без ссылки на запись.
Я считаю, что каскадное удаление полезно для обеспечения того, чтобы мертвые ссылки удалялись по умолчанию, а не вызывали исключительные исключения.
Если вы хотите удалить строки в дочерней таблице, если соответствующая строка будет удалена в родительской таблице.
Если при каскадном удалении не используется, то для ссылочной целостности будет поднята ошибка.
Если вы хотите изменить первичный ключ для обновления в чужом ключе
Одна из причин включения каскадного удаления (вместо того, чтобы делать это в коде) заключается в повышении производительности.
Случай 1: с каскадным удалением
DELETE FROM table WHERE SomeDate < 7 years ago;
Случай 2: без каскадного удаления
FOR EACH R IN (SELECT FROM table WHERE SomeDate < 7 years ago) LOOP
DELETE FROM ChildTable WHERE tableId = R.tableId;
DELETE FROM table WHERE tableId = R.tableid;
/* More child tables here */
NEXT
Во-вторых, когда вы добавляете дополнительную дочернюю таблицу с каскадным удалением, код в случае 1 продолжает работать.
Я бы включил только каскад, где семантика отношения является "частью". В противном случае какой-то идиот удалит половину вашей базы данных, когда вы это сделаете:
DELETE FROM CURRENCY WHERE CurrencyCode = 'USD'
Я пытаюсь избежать удаления или обновления, которые я явно не запрашивал на SQL-сервере.
Либо путем каскадирования, либо с помощью триггеров. Они склонны укусить вас в попу некоторое время по линии, либо при попытке отследить ошибку, либо при диагностике проблем с производительностью.
Где бы я их использовал, это гарантировать последовательность для не очень больших усилий. Чтобы получить тот же эффект, вам придется использовать хранимые процедуры.
Я, как и все остальные, обнаружил, что каскадные удаления действительно незначительно полезны (на самом деле не так много работы по удалению ссылочных данных в других таблицах - если есть много таблиц, вы просто автоматизируете это с помощью script), но очень раздражает, когда кто-то случайно каскад удаляет некоторые важные данные, которые трудно восстановить.
Единственный случай, когда я буду использовать, - это то, что данные в таблице таблиц сильно контролируются (например, ограниченные разрешения) и обновляются или удаляются только через контролируемый процесс (например, обновление программного обеспечения), который был проверен.
У меня было это в прошлом, когда администраторы баз данных и/или "Политика компании" запретили использование "On Delete Cascade" (и других) исключительно из-за плохого опыта в прошлом (в одном случае парень написал три триггера, которые в конечном итоге вызывали друг друга - 3 дня для восстановления привели к полному запрету на триггеры - из-за действий одного idjit).
Конечно, в некоторых случаях потребуются триггеры вместо "On Delete cascade", когда некоторые дочерние данные должны быть сохранены, но в других случаях его вполне допустимо использовать каскадный метод On Delete.
Разработчик должен иметь возможность принять решение на основе того, что такое разработка и что говорит спецификация. Запрет на ковер, основанный на плохом опыте, не должен быть критерием. При удалении каскада будут записываться ВСЕ дети, пользовательская сценария триггера/хранилища может не соответствовать, если его неправильно закодировано.
"Никогда не использовать" мыслительный процесс в лучшем случае драконов. Вызов вызова должен выполняться каждый раз, а изменения, производимые по мере изменения бизнес-модели.
Разве это не то, что "Разработка"?
Удаление или обновление до S, которое удаляет значение внешнего ключа, найденное в некоторых кортежах R, можно обрабатывать одним из трех способов:
Распространение называется каскадным.
Есть два случая:
‣ Если кортеж в S был удален, удалите связанные с ним кортежи R.
‣ Если кортеж в S был обновлен, обновите значение в кортежах R, которые ссылаются на него.
Если вы работаете с системой с множеством различных модулей в разных версиях, это может быть очень полезно, если каскадные удаленные элементы являются частью/владельцем держателя PK. Кроме того, все модули потребуют немедленных исправлений для очистки своих зависимых элементов до удаления владельца PK, или отношение внешнего ключа будет полностью опущено, возможно, оставит массу мусора в системе, если очистка выполняется неправильно.
Я только что представил каскадное удаление для новой таблицы пересечений между двумя уже существующими таблицами (только для пересечения, чтобы удалить), после того, как каскадное удаление было отчаито в течение довольно продолжительного времени. Это также не так уж плохо, если данные теряются.
Это, однако, плохое в таблицах списков перечислений: кто-то удаляет запись 13 - желтый из таблицы "colors", и все желтые элементы в базе данных удаляются. Кроме того, они иногда обновляются в стиле "все-вставить", что полностью исключает ссылочную целостность. Конечно, это неправильно, но как вы измените сложное программное обеспечение, которое работает в течение многих лет, с внедрением истинной ссылочной целостности, подверженной риску возникновения неожиданных побочных эффектов?
Другая проблема заключается в том, что исходные значения внешнего ключа должны сохраняться даже после удаления первичного ключа. Можно создать столбец с надгробным камнем и опцию ON DELETE SET NULL для исходного FK, но для этого снова требуются триггеры или специальный код для сохранения избыточного значения (кроме после исключения PK).
Каскадные удаления чрезвычайно полезны при реализации объектов логического супертипа и подтипа в физической базе данных.
Когда отдельные супертипы и подтипы используются для физического использования супертипов/подтипов (в отличие от свертывания всех атрибутов подтипа в одну физическую таблицу супертипа), существует взаимно-однозначное отношение между этими таблицами и проблемами, то возникает вопрос, как сохранить первичные ключи на 100% в синхронизации между этими таблицами.
Удаление каскадов может быть очень полезным инструментом для:
1) Убедитесь, что удаление записи супертипа также удаляет соответствующую запись отдельного подтипа.
2) Убедитесь, что любое удаление записи подтипа также удаляет запись супертипа. Это достигается путем реализации триггера "вместо" в таблице подтипа, который идет и удаляет соответствующую запись супертипа, которая, в свою очередь, каскад удаляет запись подтипа.
Использование каскадных удалений таким образом гарантирует, что никогда не существует сиротских записей супертипа или подтипа, независимо от того, сначала вы удаляете запись супертипа или запись подтипа.