Каскадное удаление объектов
Если в базе данных есть объекты типа Клиент , ссылающиеся на Адрес , то без дополнительных настроек при попытке удаления объекта типа Адрес произойдёт ошибка. База данных не даст удалить такой объект.
Варианты решения проблемы
Вариантов может быть очень много, в данной статье будут приведено только несколько. Технология предоставляет механизмы для решения проблемы (в основном они опираются на использование бизнес-серверов), варианты ограничиваются лишь фантазией разработчика.
Специальные интерфейсы
Для реализации каскадного удаления можно воспользоваться специально разработанными интерфейсами IReferencesCascadeDelete и IReferencesNullDelete.
Рекурсивное удаление
Это самый простой вариант, но и самый недружелюбный к пользователю: удаление 1 объекта может привести к удалению важной информации информации, связанной с данным объектом.
- В бизнес-сервере мастера (в примере — Адрес ) вычитать все объекты, ссылающиеся на удаляемый.
- Проставить всем объектам статус ObjectStatus.Deleted.
- Отправить на удаление все объекты.
- Повторить рекурсивно для всех объектов.
Фиктивный объект
Такой вариант позволяет сохранить все данные, кроме того объекта, который необходимо удалить. Однако в базе останется множество объектов, ссылающихся на несуществующий.
Стоит также отметить, что данный способ требует дополнительной обработки данных при выводе пользователю. Объекты, ссылающиеся на фиктивные, необходимо фильтровать или обрабатывать особым образом.
Вариантов решения проблемы несколько:
- создавать фиктивный объект при каждом удалении
- создать по 1 фиктивному объекту для каждого класса и “вешать” все ссылки на него.
Алгоритм для второго варианта:
- (один раз) Создать объект и записать его в базу. Запоминить его PrimaryKey, например, в файле конфигурации или в файле с константами.
- В бизнес-сервере мастера (в примере — Адрес ) вычитать все объекты, ссылающиеся на удаляемый.
- Проставить всем объектам ссылку на фиктивный объект.
- Отправить на обновление все объекты.
Фиктивное удаление
При фиктивном удалении данные на самом деле не удаляются из базы, а всего лишь помечаются как удаленные. Во все объекты добавляется какое-нибудь поле типа bool . При удалении объекта в бизнес-сервере перехватывается объект, у него меняется статус с Deleted на Altered и изменяется поле Актуально = false; .
После этого объект уходит на обновление в базу и остается в ней, но считается удаленным. Разумеется, необходимо реализовывать логику, которая будет “считать” такие объекты удаленными: при выводе информации пользователю необходимо накладывать ограничения на выводимые данные.
Пример
Необходимо доработать диаграмму классов таким образом, чтобы она поддерживала фиктивное удаление: добавить поле Актуально:bool .
Добавить логику в бизнес-сервера объектов (на примере Адреса ):
Далее, чтобы пользователю не выводились “удаленные” данные при просмотре списка объектов, требуется на соответствующий контрол наложить ограничение вида:
Каскадное удаление данных
в мире реляционных баз данных позволяет удалять связанные данные из зависимой таблицы, при удалении данных из основной таблицы. В случае модели, которую мы использовали в предыдущих примерах (две связанные таблицы 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) не поддерживают несколько отношений, которые определяют каскадное удаление, указываемое на одной таблице.
Что такое каскадное удаление
Каскадное удаление представляет автоматическое удаление зависимой сущности после удаления главной.
По умолчанию для сущностей применяется каскадное удаление, если наличие связанной сущности обязательно. Например:
Здесь свойство внешнего ключа имеет тип int , оно не допускает значения null и требует наличия конкретного значения — id связанного объекта Company (При этом то, что навигационное свойство Company допускает null, не имеет значения). То есть для объекта User обязательно необходимо наличия связанного объекта Company. Поэтому сгенерированная таблица Users будет иметь код:
В определении внешнего ключа устанавливается каскадное удаление: ON DELETE CASCADE
Аналогичная связь будет устанавливаться, если свойство-внешний ключа отсутствует, а навигационное свойство НЕ представляет nullable-тип:
Такая же таблица создается, если навигационное свойство представляет nullable-тип, но оно определено как обязательное, например, с помощью атрибута Required :
Например, добавим в базу данных 2 компании и 4 связанных с ними пользователей и затем удалим одну из компаний:
Консольный вывод программы:
Удаление главной сущности — компании привело к удалению двух зависимых сущностей — пользователей.
Теперь изменим модели, указав необязательность наличия объекта Company:
Теперь внешний ключ имеет тип Nullable<int>, то есть он допускает значение null. Когда пользователь не будет принадлежать ни одной компании, это свойство будет иметь значение null. И в этом случае скрипт таблицы Users будет выглядеть следующим образом:
Аналогичная связь будет устанавливаться, если свойство-внешний ключа отсутствует, а навигационное свойство представляет nullable-тип:
Если мы запустим ту же самую программу, то получим уже другой консольный вывод:
Настройка каскадного удаления с помощью Fluent API
В Fluent API доступны три разных сценария, которые управляют поведением зависимой сущности в случае удаления главной сущности:
Cascade : зависимая сущность удаляется вместе с главной
SetNull : свойство-внешний ключ в зависимой сущности получает значение null
Restrict : зависимая сущность никак не изменяется при удалении главной сущности
Например, установим каскадное удаление, даже если по умолчанию оно не предусматривается:
Cascade in SQL
CASCADE in SQL is used to simultaneously delete or update an entry from both the child and parent table. The keyword CASCADE is used as a conjunction while writing the query of ON DELETE or ON UPDATE . If the cascade keyword is added to a query written for the parent table, then both the parent and child tables change accordingly on the execution of the query.
Scope of the Article
- In this article, we will learn the use of the cascade keyword while writing SQL queries.
- Use of cascade while using the ON DELETE query.
- Use of cascade while using the ON UPDATE query.
Introduction
CASCADE is a keyword in MySQL relational database management system. Let us take an example in layman's terms to understand the usage of cascade keyword in SQL.
Consider a table named T1 which contains the data of all the students in a class. Table T1 has four columns, i.e., Enrollment number, name, email, and state of the student. Let us now consider two more tables, i.e., T2 and T3. Table T2 contains the marks of all the students in the Mathematics subject. Table T3 contains the marks of all the students in the Science subject. Both tables, T2 and T3, have three columns, i.e., enrollment number, name of the students, and marks of the student. Now, if there is a case where a student wants to change a class or change his name/enrollment number, we need to make certain changes in the database.
Following is the design of our MySQL database (arrangement of tables).
Case 1: If a student wants to change his class, he must delete his name from all the tables, i.e., T1, T2, and T3. As the student will be shifted to another class, so his information needs to be deleted.
Case 2: If a student wants to change his name/enrollment number, he has to update all three tables, i.e., T1, T2, and T3.
If we try to perform the scenarios provided in the above cases in a traditional fashion, the changes would need to be done manually in all three tables. We use the keyword CASCADE to overcome this problem in SQL queries. With a single command, we can change all three tables simultaneously.
Using a similar example, we will learn how to use CASCADE in SQL while deleting/updating an entry in the parent and child table. To do so, we will define our database more efficiently.
Setting up Database
As we have three tables, T1, T2, and T3, each table should be in the following format.
Table T1
Primary key: Enrollment number (int); name (varchar(20)), email-id (varchar(20)), and State of the student (varchar(20)) are regular columns.
Tables T2 & T3
Primary key: Enrollment number (int), name (varchar(20)); Marks of the student (int) is a regular column.
Creating Table T1
To see all the columns created in Table T1, run the following command.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| Enrollment | Int | NO | PRI | NULL | |
| Student_Name | varchar(20) | YES | NULL | ||
| Email_id | varchar(20) | YES | NULL | ||
| State | varchar(20) | YES | NULL |
Creating Table T2 and T3.
As we can see, while creating tables T2 and T3, we added the keywords ON DETELE CASCADE and ON UPDATE CASCADE on the line where the FOREIGN KEY is declared.
Lets us check all the columns from table T2 and T3.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| Enrollment | int | NO | PRI | NULL | |
| Student_Name | varchar(20) | NO | PRI | NULL | |
| Marks | int | YES | NULL |
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| Enrollment | int | NO | PRI | NULL | |
| Student_Name | varchar(20) | NO | PRI | NULL | |
| Marks | int | YES | NULL |
Let us insert some data into the table T1 (Students in the class).
Use the following query to view all the data of the Class table.
| Enrollment | Student_Name | Email_id | State |
|---|---|---|---|
| 1 | Rishab P | rishab@gmail.com | WB |
| 2 | Mayank G | mayank@gmail.com | JH |
| 3 | Rajat A | rajat@gmail.com | UP |
| 4 | Debojeet J | debojeet@gmail.com | WB |
| 5 | Soham M | soham@gmail.com | MH |
Now, insert data into tables T2 and T3.
Inserting values into the Science table.
Printing all the data from the Science table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 1 | Rishab P | 85 |
| 3 | Rajat A | 87 |
| 5 | Soham M | 95 |
Inserting values into the Mathematics table.
Printing all the data from the Mathematics table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 1 | Rishab P | 90 |
| 2 | Mayank G | 86 |
| 4 | Debojeet J | 83 |
We have created tables T1, T2, and T3 and added the data to the tables.
ON DELETE CASCADE in SQL
In this section, we will discuss the ON DELETE CASCADE method to solve Case 1, which we have seen in the introduction. If the student Rishab P changes his Class, then his name must be removed from all the tables, i.e., T1, T2, and T3.
Deleting an entry of a particular student from all three tables needs three queries to be executed, but while setting up the database of tables T2 and T3, we have used an ON DELETE CASCADE keyword. Because of the CASCADE keyword, we can delete the entry from the Parent Table T1, and it will automatically delete the name of the student from the table T2 and T3.
Example of ON DELETE CASCADE in SQL
Deleting the entry of student Rishab P from the Class table.
As we have only deleted the entry from the Class table, it will automatically reflect the changes in the other two tables also.
Displaying the data of the Class table.
| Enrollment | Student_Name | Email_id | State |
|---|---|---|---|
| 2 | Mayank G | mayank@gmail.com | JH |
| 3 | Rajat A | rajat@gmail.com | UP |
| 4 | Debojeet J | debojeet@gmail.com | WB |
| 5 | Soham M | soham@gmail.com | MH |
Displaying the data of Mathematics table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 2 | Mayank G | 86 |
| 4 | Debojeet J | 83 |
Displaying the data of Science table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 3 | Rajat A | 87 |
| 5 | Soham M | 95 |
As we can see from the above output, the entry of the student named Rishab P has been removed from all three tables.
ON UPDATE CASCADE in SQL
The student with enrollment number 1 was deleted when we executed the query in the previous section. So, we will now update the enrollment number of the last student in the table to the value 1. The last student has an enrollment number equal to 5 currently. The entry for the student with enrollment number 5 is present in two tables, i.e., the Class and Science table.
To update both tables traditionally, it will take two SQL queries. But, as we have added a keyword, ON UPDATE CASCADE while setting up the parent table's database, the Science table entry will automatically get updated.
Example of ON UPDATE CASCADE in SQL
Updating the enrollment number of the student having enrollment 5 (updating it to 1).
We have only updated the entry from the Class table, but we can verify that it will also automatically reflect the changes in the other tables.
Displaying the data of the Class table.
| Enrollment | Student_Name | Email_id | State |
|---|---|---|---|
| 1 | Soham M | soham@gmail.com | MH |
| 2 | Mayank G | mayank@gmail.com | JH |
| 3 | Rajat A | rajat@gmail.com | UP |
| 4 | Debojeet J | debojeet@gmail.com | WB |
Displaying the data of Science table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 1 | Soham M | 95 |
| 3 | Rajat A | 87 |
As we can see in the above table that the student having the enrollment 5 is changed it enrollment to 1.
ON INSERT CASCADE in SQL
Many people find this title amusing since it refers to a fictitious feature that people wrongly mention when discussing cascade constraints. The main idea of using ON INSERT CASCADE is that whenever a data entry is made in the parent table, and we also want to make the same entry in the child table, we use this cascade. This part will take a different example to understand ON INSERT CASCADE.
Example of ON INSERT CASCADE in SQL
First, we will create two tables, i.e., item and stock. The first table item will be the parent table, and the second table stock will be the child table. Whenever we make an entry in the item table, the stock table gets updated automatically by entering new data.
Query to create Item Table
Query to create Stocks Table
Here, the item table has three columns, i.e., Id (Primary Key), Name, and the product's price. The stock table has two columns, i.e., Id(Primary Key, Foreign Key reference from Id of item table), and Quantity.
Now, whenever a new product is launched, we need to insert the product name into the item table and set that item's quantity to zero in the stocks table. Manually this can be done by executing two queries, but using the ON INSERT CASCADE technique, this can be done within a single query.
To do so, we need to use TRIGGERS.
Implementing ON INSERT CASCADE
The above query will automatically insert the data into the stocks table.