Как сделать каскадное обновление и удаление sql

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

в мире реляционных баз данных позволяет удалять связанные данные из зависимой таблицы, при удалении данных из основной таблицы. В случае модели, которую мы использовали в предыдущих примерах (две связанные таблицы 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 и добавим следующий код:

using System; using System.Linq; using System.Collections.Generic; using System.Data.Entity; using CodeFirst; namespace ProfessorWeb.EntityFramework < public partial class CascadeDelete : System.Web.UI.Page < protected void Save_Click(object sender, EventArgs e) < Database.SetInitializer( new DropCreateDatabaseIfModelChanges()); // Создать заказчика Customer customer = new Customer < FirstName = "Василий", LastName = "Пупкин", Age = 20, // Добавим заказы для этого покупателя Orders = new List< new Order < ProductName = "Товар 1", Quantity = 4, PurchaseDate = DateTime.Now >, new Order < ProductName = "Товар 2", Quantity = 2, PurchaseDate = DateTime.Now >, new Order < ProductName = "Товар 3", Quantity = 5, PurchaseDate = DateTime.Now >, > >; // Вставить заказчика в базу данных SampleContext context = new SampleContext(); context.Customers.Add(customer); context.SaveChanges(); > protected void Delete_Click(object sender, EventArgs e) < SampleContext context = new SampleContext(); // Извлечь нужного покупателя из таблицы вместе с заказами Customer customer = context.Customers .Include(c =>c.Orders) .FirstOrDefault(c => c.FirstName == "Василий"); // Удалить этого покупателя if (customer != null) < context.Customers.Remove(customer); context.SaveChanges(); >> > >

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

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

using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace CodeFirst < public class Customer < public int CustomerId < get; set; >public string FirstName < get; set; >public string LastName < get; set; >public string Email < get; set; >public int Age < get; set; >public byte[] Photo < get; set; >public List Orders < get; set; >> public class Order < public int OrderId < get; set; >public string ProductName < get; set; >public string Description < get; set; >public int Quantity < get; set; >public DateTime PurchaseDate < get; set; >[ForeignKey("Customer")] public int UserId < get; set; >public Customer Customer < get; set; >> >

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

Данные, вставленные в таблицы в нашем примере

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

// . protected void Delete_Click(object sender, EventArgs e) < SampleContext context = new SampleContext(); // Извлечь нужного покупателя из таблицы вместе с заказами Customer customer = context.Customers .FirstOrDefault(c =>c.FirstName == "Василий"); // Удалить этого покупателя if (customer != null) < context.Customers.Remove(customer); context.SaveChanges(); >>

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

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

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

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

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

protected override void OnModelCreating(DbModelBuilder modelBuilder) < modelBuilder.Entity() .HasMany(c => c.Orders) .WithRequired(o => o.Customer) .WillCascadeOnDelete(false); >

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

Ошибка удаления связанных данных при отключенном режиме каскадного удаления

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

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

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

Каскадное обновление и удаление с различием первичных ключей в главной и зависимой таблицах

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

CREATE TABLE [dbo].[Персонажи]( Код_персонажа int NOT NULL, Код_пользователя int NOT NULL, Имя_персонажа char(30) NOT NULL, История_персонажа nvarchar(max) NULL, Уровень_персонажа int NOT NULL, Особые_умения_персонажа nvarchar(max) NULL, primary key (Код_персонажа) ); create table Пользователи ( Код_пользователя int NOT NULL, Электронная_почта nvarchar(max) NOT NULL, Логин char(10) NOT NULL, Пароль nvarchar(max) NOT NULL, Никнейм char(10) NOT NULL, Уровень_пользователя int NOT NULL, primary key (Код_пользователя), Foreign key (Код_пользователя) references Персонажи(Код_пользователя) ON DELETE CASCADE ON UPDATE CASCADE );

Ошибка возникает в объявлении внешнего ключа

Foreign key (Код_пользователя) references Персонажи(Код_пользователя)

Есть ли способ связать эти таблицы с настроенными каскадным обновлением и удалением?

Вложения

Персонажи.txt (912 байт, 6 просмотров)

SQL DELETE/UPDATE: удаление/обновление нескольких таблиц в один запрос

Доброго времени суток! Никак не могу найти ответа на этот вопрос: можно ли удалить запись сразу из нескольких таблиц? Ситуация такая: есть главная таблица и ещё несколько. Все они связаны через ключ:

главная.id = следующая1.id = следующая2.id = .

Сработает ли такое удаление:

DELETE FROM главная, следующая1, следующая2, . WHERE

Или нужно будет посылать отдельные DELETE-ы в каждую таблицу? (Если не использовать foreign key.) Аналогичный вопрос относительно Update: необходимо обновить в этих таблицах список полей (имена полей соответственно для каждой таблицы свои). Можно ли собрать все эти таблицы JOIN-ом в одну и её обновить? Что-то типа:

UPDATE Главная INNER JOIN следующая1 ON Главная.ID = Следующая1.ID SET .

Или вариант только один: обновлять каждую таблицу отдельно? Заранее благодарен!

Каскадные обновления и каскадные удаления

Каскадные обновления и каскадные удаления – весьма полезные свойства процессора баз данных SQL Server. И вот почему.

• Каскадные обновления. При изменении значения первичного ключа таблицы связанные данные во внешних ключах, относящихся к этой таблице, также изменяются, отражая изменения в первичном ключе. Следовательно, если вы измените идентификатор ID клиента Хокки Марта (Hockey Mart) в таблице tblCustomer с 48 на 72, то значение поля CustomerID всех заказов, сгенерированных этим Хокки Мартом в таблице tblOrder, автоматически изменится с 48 на 72. В рабочем приложении редко приходится изменять значения ключа (основная концепция заключается в том, что ключ должен быть уникальным и неизменным), но если это все-таки приходится делать, то все каскадные обновления этого ключа во внешних ключах будут выполнены автоматически.

• Каскадные удаления. При удалении записи в таблице все записи, связанные с этой записью в соответствующих таблицах, автоматически удаляются. Следовательно, если вы удалите запись для Хокки Марта в таблице tblCustomer, все записи в таблице tblOrder для клиента Хокки Марта автоматически удаляются.

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

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

Для проверки возможностей каскадного удаления с помощью окна Server Explorer выполните перечисленные ниже действия.

1. Убедитесь в том, что между таблицами tblCustomer и tblOrder задано отношение с каскадным удалением. (Для проверки или указания этого ограничения воспользуйтесь схемой базы данных.)

2. Создайте запись о новом клиенте, щелкнув правой кнопкой мыши на таблице tblCustomer узла Tables в окне Server Explorer, выбрав в контекстном меню команду Retrieve Data from Table и введя необходимые данные. Запомните идентификатор ID, присвоенный процессором базы данных новому клиенту, потому что он потребуется для создания заказов данного клиента. Пусть эта таблица остается открытой, потому что она нам еще понадобится чуть позже.

3. Откройте таблицу tblOrder и создайте 2-3 заказа для нового клиента. Для этого укажите в поле CustomerID идентификатор ID, присвоенный процессором базы данных новому клиенту. Пусть эта таблица также остается открытой.

4. Вернитесь к таблице tblCustomer и попытайтесь удалить запись с данными этого клиента, щелкнув правой кнопкой мыши на левом конце записи и выбрав из контекстного меню команду Delete.

5. После этого на экране появится диалоговое окно с предупреждением и просьбой подтвердить удаление данных. Щелкните на кнопке Yes.

6. Вернитесь к таблице tblOrder, и вы обнаружите, что заказы этого клиента не удалены. Что произошло? На самом деле они удалены, но дают устаревшее представление данных. Для его обновления нужно выбрать команду меню Query?Run (Запрос1?Запуск). После этого вид таблицы будет обновлен и связанные заказы данного клиента будут удалены благодаря параметрам каскадного удаления.

Как сделать каскадное обновление и удаление sql

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

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

Каскадное обновление и удаление с различием первичных ключей в главной и зависимой таблицах

SQL DELETE/UPDATE: удаление/обновление нескольких таблиц в один запрос

Каскадные обновления и каскадные удаления

Читайте также

4.11.3. Примеры удаления ipchains-правил

Программы для удаления Spyware и Adware

Каскадные (иерархические) таблицы стилей

Каскадные таблицы стилей — CSS

Проблема удаления объекта “Singleton”.

Урок 27. Три способа удаления эффекта красных глаз

82. Используйте подходящие идиомы для реального уменьшения емкости контейнера и удаления элементов

4.4. Обеспечение удаления смахиванием в ячейках табличных видов

12.5.3. Алгоритмы удаления и подстановки

18.5.8. Удаления, выполняемые с помощью редактора sed

У15.8 Каскадные или «шагающие» (walking) меню

Обновления BIOS

Компьютер зависает даже после удаления программы

3.3. КАСКАДНЫЕ ТЕХНОЛОГИЧЕСКИЕ ПОДХОДЫ

Добавить комментарий Отменить ответ