Что такое каскадное удаление sql

Руководство по проектированию реляционных баз данных. Каскадное удаление данных

Информация в статье относится к 5-й части руководства.

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

Введение.

Ближе к сути.

О внешних ключах было рассказано в переводах, останавливаться не буду на этом. Расскажу о “спутнике”.

В случае, если не знать теории, следить за связями данных придется самостоятельно. Альтернативным вариантом является возложение этой задачи на базу данных. Что же за, так названное, слежение за связями данных? Чтобы понять, нужен пример.

У нас есть какие-то вещи. Они разбросаны, их много. Мы хотим навести порядок. Порядок – это, зачастую, классификация (категоризация) и опись. Мы хотим порядка, при этом, мы умеем работать с базами данных и не хотим ничего писать на бумаге. Мы записываем все вещи “в столбик”. Далее мы просматриваем список и определяем категории к которым относятся вещи.

Пусть это часть наших вещей, остальные не рассматриваем:

• книга 1
• книга 2
• книга 3
• компьютерная мышка
• клавиатура
• ручка
• степлер

Книга 1, книга 2, книга 3 – это книги, как ни странно.
Компьютерная мышка, клавиатура – это компьютерная периферия.
Ручка, степлер – это канцелярские принадлежности.

Мы создаем две таблицы в базе данных: categories (категории) и stuff(вещи).

1 | книги
2 | компьютерная периферия
3 | канцелярские принадлежности

stuff_id | category_id | name

1 | 1 | книга 1
2 | 1 | книга 2
3 | 1 | книга 3
4 | 2 | компьютерная мышка
5 | 2 | клавиатура
6 | 3 | ручка
7 | 3 | степлер

P.S. Изображения с habrastorage.org не отображаются.

Итого: у нас есть книги, компьютерная периферия, канцелярские принадлежности.

Мы захотели выкинуть или подарить все наши книги, не хотим видеть эти вещи, как категорию, у себя дома, нам нравятся электронные книги. Мы удаляем из таблицы категорий категорию “книги”. При этом, у нас остаются вещи из этой категории в другой таблице, мы ссылаемся на эти категории в таблице вещей. Это и называется нарушением ссылочной целостности. Казалось бы, нет у нас категории, а значит и нет книг, но записи в таблице вещей остались и вещей-то у нас много и в будущем положение дел может повториться и повторится и тогда у нас будет бардак, много лишней информации и все вытекающие последствия как в удобстве работы с нашей информацией, так и в технической части при работе с базой (напр., поиск информации). И тут приходит понимание, что нам нужно работать с двумя таблицами, следить в каких случаях связи могут быть нарушены, сломаны и совершать какие-то телодвижения и тут есть два варианта: самостоятельно делаем это или, вот тут знание – сила, мы может переложить эту головную боль на базу данных.

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

Сломаться связи могут (если говорить “правильным” языком – ссылочная целостность может нарушиться) в следующих случаях:

• обновляется внешний ключ (ссылка на идентификатор в таблице категорий) в строке-потомке. Мы обновляем категорию (цифру, идентификатор этой категории) у какой-то вещи, и ошибаемся, нет такой категории. И… имеем подвисшую в воздухе вещь.
• добавляется новая строка-потомок. Добавляем новую вещь, а она не принадлежит ни одной категории. Кстати говоря, добавить категорию мы можем без вещей. У нас так устроена база данных, что вещь не может быть без категории, а категория может, она ведь не ссылается на вещь.
• удаление строки-предка. Это как раз то, что было в нашем случае. Удалили категорию, а вещи остались.
• обновление первичного ключа в строке-предке. Мы поменяли идентификатор категории, а на прежний идентификатор у нас ссылаются определенные вещи. Итог: часть вещей опять в подвешенном состоянии.

Средства поддержания ссылочной целостности SQL (скажу сразу, наперед, когда будет нужно – поймете; если говорить про РСУБД MySQL, то использование этих средств вместе с внешними ключами возможно только для таблиц InnoDB; внешние ключи можно искользовать в MyISAM, создавая определенную структуру даных, но тогда вся головная боль по слежению за связями ложится на пользователя) позволяют обрабатывать указанные случаи.

И вот как решаются эти проблемы (в порядке перечисления):

• При обновлении в таблице-потомке проверяется новое значение внешнего ключа. Если указываемого значения нет среди первичных ключей таблицы-предка, то возвращается ошибка.
  В нашем случае, если мы изменяем для вещи номер категории, а он не существует.
• При добавлении новой строки-потомка. Если указываемое значение внешнего ключа не существует среди первичных ключей таблицы-предка, то возвращается ошибка.
  В нашем случае, если мы добавляем вещь и указываем для нее номер несуществующей категории.

Теперь два последних. Тут положение дел более интересное.

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

Где необязательные конструкции ON DELETE и ON UPDATE позволяют задать те самые варианты решения проблемы, которые рассмотрены выше. А эти ключевые слова именуют их:

CASCADE – при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, автоматически удаляются или обновляются записи со ссылками на это значение в таблице-потомке. В нашем случае, если мы удалим категорию, то удалятся и все вещи, относящиеся к этой категории в таблице вещей. Если мы обновим идентификатор у категории, то у вещей, которые ссылались на эту категорию, идентификатор также изменится на новый.
То самое, каскадное, но, как видите, не только удаление.

SET NULL – при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, значения внешнего ключа в таблице-потомке устанавливаются в NULL.
В нашем случае, если мы удалим или обновим идентификатор категории в таблице категорий, то у всех вещей, которые ссылались, относились, к данной категории в поле с идентификатором категории будет выставлено NULL.

NO ACTION — при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке никаких действий предприниматься не будет.
В нашем случае, если мы удалим или обновим идентификатор категории в таблице категорий, то это никак не повлияет на таблицу вещей.

RESTRICT – если в таблице-потомке есть записи, которые ссылаются на существующий первичный ключ в таблице-потомке, то при удалении или обновлении записи с первичным ключом в таблице-предке возвратится ошибка.
В нашем случае, если мы попробуем обновить или изменить идентификатор категории при том, что есть вещи, относящиеся к этой категории, то мы получим ошибку.

SET DEFAULT – тут понятно из названия, что при удалении или обновлении записи в таблице-предке, которая содержит первичный ключ, в таблице-потомке соответствующим записям будет выставлено значение по умолчанию. Есть одно “НО”. В РСУБД MySQL это ключевое слово не используется.

А теперь вновь – к каскадному удалению данных. Почему именно оно на слуху? Почему спросили про него в первую очередь, не смотря на то, что оно лишь одно из. Наверное, потому, что каскадное удаление данных наиболее частое решение проблемы.

Каскадное удаление данных

в мире реляционных баз данных позволяет удалять связанные данные из зависимой таблицы, при удалении данных из основной таблицы. В случае модели, которую мы использовали в предыдущих примерах (две связанные таблицы Customer и Order), при использовании каскадного удаления, удаление данных покупателя будет вести к удалению всех связанных с ним заказов. В SQL Server и T-SQL каскадное удаление реализовано в виде опций ON DELETE CASCADE и ON UPDATE CASCADE, которые указываются при объявлении внешнего ключа таблицы.

По умолчанию Code-First включает каскадное удаление для внешних ключей, не поддерживающих значение NULL, используя соответствующий SQL-код при создании таблицы. В предыдущей статье мы описали, как указать Code-First на то, что внешний ключ должен обязательно использоваться (т.е. поддерживать ограничение NOT NULL). Давайте вспомним, как это сделать:

Можно явно указать свойство внешнего ключа, тогда Code-First по умолчанию использует для него значение NOT NULL в базе данных. В примерах мы использовали внешние ключи CustomerId и UserId.

Если внешний ключ не указан в классе модели, тогда Code-First автоматически генерирует его, разрешая использовать NULL. Чтобы это изменить, можно использовать атрибут Required к навигационному свойству модели.

Давайте рассмотрим пример использования каскадного удаления на примере нашего тестового проекта ASP.NET. Для этого добавим новую веб-форму CascadeDelete.aspx и добавим следующий код:

В этой форме используются две кнопки для удаления и сохранения данных. В коде обработчика Save_Click происходит создание произвольного объекта Customer с тремя связанными объектами Order, после чего эти данные вставляются в базу. В коде обработчика Delete_Click мы сначала извлекаем данные нужного заказчика из базы данных, а затем удаляем его. Обратите внимание, что здесь используется «жадная загрузка» (eager loading), т.к. мы вызываем метод Include(). Это означает, что помимо данных покупателя, будут извлечены все данные связанных с ним заказов. Фактически каскадное удаление в данном случае не нужно, т.к. мы уже извлекли все связанные заказы.

Модель данных на текущий момент выглядит следующим образом:

Запустите наш пример и откройте в браузере веб-форму CascadeDelete.aspx и щелкните по кнопке “Сохранить”. Entity Framework воссоздаст базу данных (если модель изменилась) и добавит новые данные в таблицы Customers и Orders. Чтобы в этом убедиться, используйте средства Visual Studio или SQL Server Management Studio для просмотра данных:

Нажмите на кнопку “Удалить”, чтобы убедиться, что данные покупателя и связанные с ним заказы удаляются корректно. При этом Entity Framework отправит четыре запроса DELETE базе данных (три для каждого заказа и один для покупателя). Давайте теперь отключим использование жадной загрузки и явно используем каскадное удаление. Ниже показан измененный код обработчика Delete_Click:

Здесь мы удалили вызов метода Include() и теперь Code-First не известно о связанных с покупателем заказов. В отличие от предыдущего примера, здесь Entity Framework отправит один запрос DELETE для удаления покупателя. При выполнении этого запроса сработает средство каскадного удаления и SQL Server найдет связанные заказы, удалит сначала их, а уже потом удалит покупателя.

Отключение каскадного удаления данных

Возможно вам понадобиться отключить использование каскадного удаления в базе данных. Как описывалось выше, чтобы сделать это, можно удалить явное определение первичного ключа из класса модели и положиться на автоматическую генерацию первичного ключа с помощью Code-First (при этом Code-First указывает поддержку NULL для этого ключа). Также можно воспользоваться средствами Fluent API для явного отключения каскадного удаления, если, например, требуется сохранить объявление первичного ключа в классе модели.

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

Отключить или включить каскадное удаление в Fluent API позволяет метод WillCascadeOnDelete(), которому передается логический параметр. Использование этого метода показано в примере ниже:

Если вы запустите приложение и попробуете удалить данные, используя второй пример обработчика Delete_Click, то возникнет исключение, показанное на рисунке ниже:

Как уже описывалось ранее, при удалении данных из родительской таблицы, необходимо позаботиться об удалении данных из производной таблицы. Мы забыли извлечь данные связанных заказов из таблицы Orders и поэтому SQL Server вернул ошибку при попытке удаления данных только покупателя. Если вы теперь включите “жадную загрузку” с помощью метода Include() в обработчике Delete_Click, то эта ошибка исчезнет, но возникнет новая – как описывалось выше, в этом случае Code-First отправит четыре запроса на удаление и при удалении первого заказа Code-First установит для свойства Order.Customer значение NULL, а т.к. наша модель содержит внешний ключ, который не может иметь значение NULL возникнет ошибка.

Из этого описания можно сделать вывод, что для данного примера отключение каскадного удаления нельзя применить, но тогда возникает вопрос, зачем вообще отменять каскадное удаление? По своему опыту скажу, что отключение каскадного удаления используется в основном при получении циклической ссылки между таблицами в сложных базах данных. Такая ссылка может возникнуть, если между несколькими таблицами используется отношение “родительская-дочерняя” и последняя зависимая таблица неожиданно ссылается на одну из родительских таблиц. Проблема циклических ссылок проявляется не только при удалении данных, а также при их обновлении (операция UPDATE в T-SQL).

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

Delete And Update Cascade in SQL Server

In this article, we learn about delete and update cascade in SQL Server.

• First I will create a two table: EMP table and EMP_Address table. And I will apply the primary key on both of the tables.

TABLE 1:- EMP

CREATE TABLE EMP(

ID INT PRIMARY KEY,

TABLE 2:- EMP_Address

CREATE TABLE EMP_Address(

ID INT PRIMARY KEY,

• After that, I will create a foreign key relationship with two tables. And Delete and Update cascade apply to that table.
• Query:

ALTER TABLE [dbo].[EMP_Address]

ADD CONSTRAINT FK_EMP_Address

ON DELETE CASCADE

ON UPDATE CASCADE

• In the delete cascade, If we delete the record from the source table also it will delete the record from another table.
• Query: DELETE FROM [dbo].[EMP] WHERE [Id]=1
• Run the query and check the records by using the select query. In both tables, the first record is deleted.

• In the update cascade, If we update the record from the table also it will update the record from another table.
• Query: UPDATE [dbo].[EMP] SET where [ID] = 4
• Check the tables again to see the record by using the select query.

• After executing the table Id 4 is updated into 5 in both tables.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: —

Cascade Delete

Эффективное управление данными в базах данных жизненно важно в современном цифровом ландшафте. Каскадное удаление, функция реляционных баз данных, играет важную роль в поддержании целостности данных, упрощая обработку связанных записей в отношениях родитель-ребенок. Цель данного обсуждения — обеспечить глубокое понимание Cascade Delete, его реализации, преимуществ, недостатков и лучших практик для максимизации его эффективности.

Мы рассмотрим основы реляционных систем управления базами данных, ограничения первичных и внешних ключей, а также механизм каскадного удаления. Также будет рассмотрена практическая реализация этой функции в различных системах баз данных, таких как MySQL, PostgreSQL и SQL Server, а также потенциальные подводные камни и узкие места в производительности.

Изучив лучшие практики и стратегии для Cascade Delete, читатели смогут принимать обоснованные решения, максимизируя преимущества и снижая риски. Этот всесторонний анализ поможет администраторам баз данных, разработчикам и ИТ-специалистам лучше управлять связанными записями и повысить производительность базы данных.

Что такое внешний ключ с Cascade DELETE в SQL Server?

Внешний ключ с Cascade DELETE в SQL Server — это мощная функция, используемая для поддержания ссылочной целостности между связанными таблицами в реляционной системе управления базами данных. Внешний ключ — это столбец или набор столбцов, которые ссылаются на первичный ключ другой таблицы, тем самым устанавливая связь между двумя таблицами. Опция Cascade DELETE обеспечивает выполнение правила, которое автоматически удаляет соответствующие дочерние записи при удалении родительской записи.

Например, рассмотрим приложение электронной коммерции с двумя таблицами: ‘Orders’ и ‘Order_Items.’ Таблица ‘Orders’ содержит общую информацию о заказе, а таблица ‘Order_Items’ — отдельные товары, связанные с каждым заказом. Определив внешний ключ Cascade DELETE в таблице ‘Order_Items’ , ссылающийся на первичный ключ таблицы ‘Orders’ , вы гарантируете, что при удалении заказа из таблицы ‘Orders’ все связанные с ним элементы в таблице ‘Order_Items’ также будут автоматически удалены. Этот механизм помогает поддерживать согласованность данных и предотвращать появление осиротевших записей, не имеющих надлежащих связей с родительской таблицей.

Когда возникает каскадное поведение

Каскадное поведение при разработке программного обеспечения обычно возникает, когда действие или изменение в одной части системы вызывает серию связанных действий или последствий в других частях системы. Такое поведение обычно наблюдается в различных контекстах, например, каскадные таблицы стилей ( CSS ) в веб-разработке, каскадные обновления и удаления в системах управления базами данных или распространение событий в программных приложениях. В контексте баз данных каскадное поведение возникает, когда определенные операции манипулирования данными, такие как обновление или удаление, выполняются в родительских таблицах, вызывая соответствующие изменения в связанных дочерних таблицах.

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

Каскад PostgreSQL DELETE

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

Например, рассмотрим приложение блога с двумя таблицами: «Authors» и «Posts» . Таблица «Authors» содержит информацию об отдельных авторах, а таблица «Posts» — подробные сведения о постах в блоге, созданных этими авторами. Определив ограничение внешнего ключа с DELETE CASCADE для таблицы «Posts» , ссылающееся на первичный ключ таблицы «Authors» , вы гарантируете, что при удалении автора из таблицы «Authors» все связанные с ним записи в блоге в таблице «Posts» также будут автоматически удалены. Этот механизм помогает поддерживать согласованность данных во всем приложении, предотвращая появление бесхозных постов и обеспечивая обновление или удаление связанных данных одновременно с изменениями в родительской таблице.

Когда использовать каскад DELETE в Postgres?

DELETE CASCADE в Postgres следует использовать, когда вы хотите поддерживать ссылочную целостность и согласованность данных между связанными таблицами в вашем приложении, особенно когда удаление записей из родительской таблицы может потенциально оставить осиротевшие записи в дочерних таблицах. Используя DELETE CASCADE, вы гарантируете, что при удалении родительской записи все связанные с ней записи в дочерней таблице также автоматически удаляются, предотвращая несогласованность данных и сохраняя связи между сущностями.

Например, рассмотрим платформу онлайн-обучения с двумя таблицами: «Courses» и «Enrollments.» Таблица «Courses» содержит информацию об отдельных курсах, а в таблице «Enrollments» записаны студенты, зачисленные на каждый курс. Если курс удаляется из таблицы «Courses» , очень важно удалить все связанные с ним записи о зачисленных студентах из таблицы «Enrollments» , чтобы сохранить согласованность данных. Реализовав ограничение внешнего ключа с DELETE CASCADE в таблице «Enrollments» со ссылкой на первичный ключ таблицы «Courses» , вы обеспечите, что удаление курса приведет к удалению всех связанных с ним записей о зачислении.

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

Как использовать каскадное удаление DELETE в Postgres?

Чтобы использовать DELETE CASCADE в Postgres, необходимо создать ограничение внешнего ключа на дочерней таблице, указав опцию CASCADE при определении отношений между дочерней и родительской таблицами. Это обеспечит, что при удалении записи в родительской таблице все связанные с ней записи в дочерней таблице также будут автоматически удалены. Вот пошаговый процесс реализации DELETE CASCADE в Postgres:

• Сначала определите родительскую и дочернюю таблицы. Например, рассмотрим систему управления библиотекой с двумя таблицами: «Authors» и «Books.» Таблица «Authors» содержит информацию об отдельных авторах, а таблица «Books» — сведения о книгах, написанных этими авторами.
• Создайте родительскую таблицу, например, «Authors,» с колонкой первичного ключа:

• Создайте дочернюю таблицу, например, «Books» , с колонкой внешнего ключа, ссылающейся на первичный ключ родительской таблицы, и укажите опцию DELETE CASCADE:

При наличии ограничения внешнего ключа и DELETE CASCADE, когда автор удаляется из таблицы «Authors» , все связанные с ним книги в таблице «Books» будут автоматически удалены, поддерживая согласованность данных и ссылочную целостность.

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

Как работает каскад DELETE в Postgres?

DELETE CASCADE в Postgres — это важный механизм для поддержания согласованности данных и ссылочной целостности в реляционных базах данных. Он гарантирует, что при удалении записи из родительской таблицы автоматически удаляются и все связанные с ней записи в дочерней таблице. Рассмотрим практический пример, иллюстрирующий работу DELETE CASCADE в Postgres:

Представьте себе систему управления университетом с двумя таблицами: «Professors» и «Courses» . В таблице «Professors» хранится информация об отдельных профессорах, а в таблице «Courses» — информация о курсах, преподаваемых этими профессорами. Каждый курс связан с одним профессором.

• Создайте таблицу «Professors» с колонкой первичного ключа:

• Создайте таблицу «Courses» с колонкой внешнего ключа, ссылающейся на первичный ключ таблицы «Professors» , и укажите опцию DELETE CASCADE:

• Теперь предположим, что вы вставили двух профессоров и несколько курсов в соответствующие таблицы:

На данный момент таблица «Courses» содержит три записи, связанные с соответствующими профессорами. Если вы решите удалить профессора Джона Доу (ID: 1) из таблицы «Professors» :

Благодаря ограничению DELETE CASCADE, Postgres автоматически удалит связанные курсы ( ‘Math 101’ and ‘Physics 101’ ) из таблицы «Courses» , гарантируя, что база данных поддерживает ссылочную целостность и согласованность данных, не оставляя бесхозных записей курсов.

Заключение

В заключение следует отметить, что эффективное управление данными в базах данных имеет решающее значение в нашем все более цифровом мире. Каскадное удаление — это мощная функция реляционных баз данных, которая упрощает обработку связанных записей в отношениях «родитель-ребенок», обеспечивая целостность и непротиворечивость данных. Изучив основы реляционных систем управления базами данных, ограничения первичных и внешних ключей, а также механизм каскадного удаления, мы обеспечили полное понимание его реализации, преимуществ, недостатков и лучших практик.

Благодаря практическим примерам и объяснениям на примере различных систем баз данных, таких как MySQL, PostgreSQL и SQL Server, читатели будут хорошо подготовлены к тому, чтобы максимально повысить эффективность Cascade Delete и одновременно снизить потенциальные риски. Этот глубокий анализ позволяет администраторам баз данных, разработчикам и ИТ-специалистам повысить производительность своих баз данных и эффективно управлять связанными записями, что в конечном итоге способствует созданию более прочной и надежной инфраструктуры данных.