Когда и зачем использовать каскадирование в SQL Server?
При настройке внешних ключей в SQL Server, при каких обстоятельствах он должен каскадироваться при удалении или обновлении, и каковы причины этого?
Это, вероятно, относится и к другим базам данных.
Больше всего я ищу конкретные примеры каждого сценария, желательно от того, кто их успешно использовал.
16 ответов
Резюме того, что я видел до сих пор:
- Некоторым людям совсем не нравится каскадирование.
Каскад Удалить
- Каскадное удаление может иметь смысл, когда семантика отношений может включать в себя эксклюзивное "является частью" описания. Например, запись OrderLine является частью своего родительского заказа, и OrderLines никогда не будут разделены между несколькими заказами. Если ордер должен был исчезнуть, OrderLine также должен был бы, и строка без ордера была бы проблемой.
- Каноническим примером для Каскадного удаления является SomeObject и SomeObjectItems, где не имеет никакого смысла, чтобы запись элементов когда-либо существовала без соответствующей основной записи.
- Вы не должны использовать Cascade Delete, если вы сохраняете историю или используете "мягкое / логическое удаление", когда вы устанавливаете только столбец удаленных битов в 1/true.
Каскадное обновление
- Каскадное обновление может иметь смысл, когда вы используете реальный ключ, а не суррогатный ключ (столбец идентификатора / автоинкремента) в разных таблицах.
- Канонический пример для Cascade Update - это когда у вас есть изменяемый внешний ключ, например, имя пользователя, которое можно изменить.
- Не следует использовать Cascade Update с ключами, которые являются столбцами Identity / autoincrement.
- Каскадное обновление лучше всего использовать в сочетании с уникальным ограничением.
Когда использовать каскадирование
- Вы можете получить дополнительное строгое подтверждение от пользователя, прежде чем разрешить каскадную операцию, но это зависит от вашего приложения.
- Каскадирование может привести к неприятностям, если вы неправильно настроили внешние ключи. Но вы должны быть в порядке, если вы делаете это правильно.
- Неразумно использовать каскадирование, прежде чем полностью его понять. Однако это полезная функция, и поэтому стоит потратить время на ее понимание.
Внешние ключи - лучший способ обеспечить ссылочную целостность базы данных. Избегать каскадов из-за магии - это все равно, что писать все на ассемблере, потому что вы не доверяете магии, стоящей за компиляторами.
Что плохо, так это неправильное использование внешних ключей, например создание их задом наперед.
Пример Хуана Мануэля является каноническим примером, если вы используете код, есть гораздо больше шансов оставить ложные DocumentItems в базе данных, которые придут и укусят вас.
Каскадные обновления полезны, например, когда у вас есть ссылки на данные, которые могут измениться, например, первичным ключом таблицы пользователей является комбинация имени и фамилии. Затем вы хотите, чтобы изменения в этой комбинации распространялись туда, где на них ссылаются.
@Aidan, та ясность, на которую вы ссылаетесь, обуславливается высокой ценой, вероятность того, что ложные данные останутся в вашей базе данных, а это не мало. Для меня это, как правило, просто незнакомство с БД и неспособность определить, какие ФК существуют, прежде чем работать с БД, которые вызывают этот страх. Или это, или постоянное неправильное использование каскада, использование его там, где сущности не были концептуально связаны, или где вы должны сохранять историю.
Я никогда не использую каскадные удаления.
Если я хочу что-то удалить из базы данных, я хочу явно указать базе данных, что я хочу удалить.
Конечно, они являются функцией, доступной в базе данных, и могут быть случаи, когда их можно использовать, например, если у вас есть таблица 'order' и таблица 'orderItem', вы можете захотеть очистить элементы при удалении порядок.
Мне нравится ясность, которую я получаю от выполнения этого в коде (или хранимой процедуре), а не от "волшебства".
По той же причине я тоже не фанат триггеров.
Стоит заметить, что если вы удалите "заказ", вы получите отчет "На 1 строку затронут", даже если каскадное удаление удалило 50 "элементов OrderItem".
Я много работаю с каскадным удалением.
Приятно знать, что тот, кто работает с базой данных, никогда не оставит нежелательных данных. Если зависимости растут, я просто изменяю ограничения на диаграмме в Management Studio, и мне не нужно настраивать sp или dataacces.
Тем не менее, у меня есть 1 проблема с каскадным удалением, и это круговые ссылки. Это часто приводит к частям базы данных, которые не имеют каскадных удалений.
Я много работаю с базами данных и редко нахожу каскадные удаления полезными. Единственный раз, когда я использовал их эффективно, это база данных отчетов, которая обновляется ночными заданиями. Я проверяю, что любые измененные данные импортируются правильно, удаляя все записи верхнего уровня, которые изменились с момента последнего импорта, а затем повторно импортируйте измененные записи и все, что с ними связано. Это избавляет меня от необходимости писать множество сложных удалений, которые смотрят снизу вверх в моей базе данных.
Я не считаю, что каскадное удаление является настолько же плохим, как и триггеры, поскольку они только удаляют данные, у триггеров могут быть самые разные неприятные вещи внутри.
В общем, я вообще избегаю реальных удалений и использую логические удаления (т. Е. Вместо этого используется битовый столбец с именем isDeleted, для которого установлено значение true).
Один пример - когда у вас есть зависимости между сущностями... то есть: Document -> DocumentItems (когда вы удаляете Document, DocumentItems не имеет причин для существования)
ON Удалить каскад:
Когда вы хотите удалить строки в дочерней таблице Если соответствующая строка удалена в родительской таблице.
Если при каскадном удалении не используется, возникнет ошибка для ссылочной целостности.
Обновление каскада:
Когда вы хотите, чтобы изменение в первичном ключе было обновлено во внешнем ключе
Я слышал о администраторах баз данных и / или "Политике компании", которые запрещают использование "Каскадного удаления" (и других) исключительно из-за плохого опыта в прошлом. В одном случае парень написал три триггера, которые в итоге вызвали друг друга. Три дня восстановления привели к полному запрету триггеров, все из-за действий одного idjit.
Конечно, иногда вместо "Каскад при удалении" необходимы триггеры, например, когда необходимо сохранить некоторые дочерние данные. Но в других случаях вполне допустимо использовать каскадный метод при удалении. Основное преимущество "Каскада при удалении" заключается в том, что он захватывает ВСЕ дочерние элементы; Пользовательская письменная процедура триггера / сохранения может не работать, если она не закодирована правильно.
Я считаю, что разработчик должен иметь возможность принимать решение на основе разработки и того, что говорится в спецификации. Запрет ковра, основанный на плохом опыте, не должен быть критерием; мыслительный процесс "Никогда не используйте" в лучшем случае драконов. Каждый раз необходимо делать суждения и вносить изменения по мере изменения бизнес-модели.
Разве не в этом вся суть развития?
Используйте каскадное удаление там, где вы хотите удалить запись с 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-сервере.
Либо через каскадирование, либо с помощью триггеров. Они имеют тенденцию кусать вас в задницу некоторое время, либо при попытке отследить ошибку, либо при диагностике проблем с производительностью.
Я бы использовал их, чтобы гарантировать постоянство при не очень больших усилиях. Чтобы получить тот же эффект, вам придется использовать хранимые процедуры.
Удаление или обновление S, которое удаляет значение внешнего ключа, найденное в некоторых кортежах R, может быть обработано одним из трех способов:
- непринятие
- распространения
- обнуление.
Распространение называется каскадным.
Есть два случая:
‣ Если кортеж в S был удален, удалите R кортежей, которые на него ссылались.
‣ Если кортеж в S был обновлен, обновите значение в R кортежей, которые к нему относятся.
я бы провел различие между
- Целостность данных
- Бизнес-логика/правила
По моему опыту, лучше всего обеспечить целостность базы данных, насколько это возможно, используя PK, FK и другие ограничения.
Однако бизнес-правила/логика IMO лучше всего реализуются с использованием кода по причине сплоченности (погуглите «связь и сплоченность», чтобы узнать больше).
Является ли каскадное удаление/обновление целостностью данных или бизнес-правилами? Конечно, это можно обсудить, но я бы сказал, что обычно это логика/правило. Например, бизнес-правило может заключаться в том, что при удалении все должно автоматически удаляться. Но также может случиться так, что никогда не будет возможности удалитьOrderесли еще естьOrderItems. Так что это может решать бизнес. Откуда мы знаем, как это правило реализуется в настоящее время? Если все это в коде, мы можем просто посмотреть на код (высокая связность). Если правило может быть реализовано в коде или может быть реализовано в виде каскада в базе данных, нам нужно искать в нескольких местах (низкая связность).
Конечно, если вы идете ва-банк, помещая свои бизнес-правила только в базу данных и используя триггеры, может иметь смысл сохраненная процедура, а затем каскад.
Я обычно рассматриваю блокировку поставщика базы данных перед использованием любых хранимых процедур или триггеров. Базу данных SQL, которая просто хранит данные и обеспечивает целостность, IMO легче перенести на другого поставщика. По этой причине я обычно не использую хранимую процедуру или триггеры.
Каскадные удаления чрезвычайно полезны при реализации логических сущностей супертипа и подтипа в физической базе данных.
Когда отдельные таблицы супертипа и подтипа используются для физической реализации супертипов / подтипов (в отличие от сворачивания всех атрибутов подтипа в одну физическую таблицу супертипа), существует одно После этого возникает взаимосвязь между этими таблицами и вопросом, как обеспечить 100% синхронизацию первичных ключей между этими таблицами.
Каскадное удаление может быть очень полезным инструментом для:
1) Убедитесь, что удаление записи супертипа также удаляет соответствующую отдельную запись подтипа.
2) Убедитесь, что любое удаление записи подтипа также удаляет запись супертипа. Это достигается путем реализации триггера удаления "вместо" в таблице подтипов, который отправляет и удаляет соответствующую запись супертипа, которая, в свою очередь, каскадно удаляет запись подтипа.
Таким образом, использование каскадного удаления гарантирует, что никогда не будет ни одной записи супертипа или подтипа, независимо от того, была ли сначала удалена запись супертипа или сначала запись подтипа.
Если вы работаете в системе с множеством разных модулей в разных версиях, это может быть очень полезно, если каскадные удаленные элементы являются частью / принадлежат владельцу PK. В противном случае всем модулям потребовались бы немедленные исправления, чтобы очистить их зависимые элементы перед удалением владельца PK, или отношение внешнего ключа было бы полностью опущено, что может привести к тому, что в системе останутся тонны мусора, если очистка не будет выполнена правильно.
Я только что ввел каскадное удаление для новой таблицы пересечений между двумя уже существующими таблицами (пересечение только для удаления), после того, как каскадное удаление не поощрялось в течение довольно долгого времени. Это также не так уж плохо, если данные будут потеряны.
Однако, это плохо для таблиц списков, подобных перечислимым: кто-то удаляет запись 13 - желтый из таблицы "colors", и все желтые элементы в базе данных удаляются. Кроме того, они иногда обновляются способом delete-all-insert-all, что приводит к полной пропущенной ссылочной целостности. Конечно, это неправильно, но как вы измените сложное программное обеспечение, работающее в течение многих лет, когда внедрение истинной ссылочной целостности может привести к неожиданным побочным эффектам?
Другая проблема заключается в том, что исходные значения внешнего ключа должны сохраняться даже после удаления первичного ключа. Можно создать столбец надгробной плиты и опцию ON DELETE SET NULL для исходного FK, но для этого снова требуются триггеры или специальный код для поддержания избыточного (кроме после удаления PK) значения ключа.