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

Данное руководство устарело. Актуальное руководство: Руководство по Entity Framework Core 7

Последнее обновление: 16.11.2020

Каскадное удаление представляет автоматическое удаление зависимой сущности после удаления главной.

По умолчанию для сущностей применяется каскадное удаление, если наличие связанной сущности обязательно. Например:

public class Company < public int Id < get; set; >public string Name < get; set; >// название компании public List Users < get; set; >> public class User < public int Id < get; set; >public string Name < get; set; >public int CompanyId < get; set; >// внешний ключ public Company Company < get; set; >// навигационное свойство > public class ApplicationContext : DbContext < public DbSetCompanies < get; set; >public DbSet Users < get; set; >public ApplicationContext() < Database.EnsureDeleted(); Database.EnsureCreated(); >protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) < optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=relationsdb;Trusted_Connection=True;"); >>

Здесь свойство внешнего ключа имеет тип int , оно не допускает значения null и требует наличия конкретного значения — id связанного объекта Company. То есть для объекта User обязательно необходимо наличия связанного объекта Company. Поэтому сгенерированная таблица Users будет иметь код:

CREATE TABLE [dbo].[Users] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, [CompanyId] INT NOT NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Users_Companies_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([Id]) ON DELETE CASCADE );

В определении внешнего ключа устанавливается каскадное удаление: ON DELETE CASCADE

Например, добавим в базу данных 2 компании и 4 связанных с ними пользователей и затем удалим одну из компаний:

using (ApplicationContext db = new ApplicationContext()) < // добавляем начальные данные Company microsoft = new Company < Name = "Microsoft" >; Company google = new Company < Name = "Google" >; db.Companies.AddRange(microsoft, google); db.SaveChanges(); User tom = new User < Name = "Tom", Company = microsoft >; User bob = new User < Name = "Bob", Company = google >; User alice = new User < Name = "Alice", Company = microsoft >; User kate = new User < Name = "Kate", Company = google >; db.Users.AddRange(tom, bob, alice, kate); db.SaveChanges(); // получаем пользователей var users = db.Users.ToList(); foreach (var user in users) Console.WriteLine($""); // Удаляем первую компанию var comp = db.Companies.FirstOrDefault(); db.Companies.Remove(comp); db.SaveChanges(); Console.WriteLine("\nСписок пользователей после удаления компании"); // снова получаем пользователей users = db.Users.ToList(); foreach (var user in users) Console.WriteLine($""); >

Консольный вывод программы:

Bob Tom Alice Kate Список пользователей после удаления компании Bob Kate

Удаление главной сущности — компании привело к удалению двух зависимых сущностей — пользователей.

Теперь изменим модели, указав необязательность наличия объекта Company:

public class Company < public int Id < get; set; >public string Name < get; set; >// название компании public List Users < get; set; >> public class User < public int Id < get; set; >public string Name < get; set; >public int? CompanyId < get; set; >// внешний ключ public Company Company < get; set; >// навигационное свойство >

Теперь внешний ключ имеет тип Nullable, то есть он допускает значение null. Когда пользователь не будет принадлежать ни одной компании, это свойство будет иметь значение null. И в этом случае скрипт таблицы Users будет выглядеть следующим образом:

CREATE TABLE [dbo].[Users] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, [CompanyId] INT NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Users_Companies_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([Id]) );

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

Bob Tom Alice Kate Список пользователей после удаления компании Bob Tom Alice Kate

Настройка каскадного удаления с помощью Fluent API

В Fluent API доступны три разных сценария, которые управляют поведением зависимой сущности в случае удаления главной сущности:

Cascade : зависимая сущность удаляется вместе с главной
SetNull : свойство-внешний ключ в зависимой сущности получает значение null
Restrict : зависимая сущность никак не изменяется при удалении главной сущности

Например, установим каскадное удаление, даже если по умолчанию оно не предусматривается:

using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata; using System.Collections.Generic; public class ApplicationContext : DbContext < public DbSetCompanies < get; set; >public DbSet Users < get; set; >public ApplicationContext() < Database.EnsureDeleted(); Database.EnsureCreated(); >protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) < optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=relationsdb;Trusted_Connection=True;"); >protected override void OnModelCreating(ModelBuilder modelBuilder) < modelBuilder.Entity() .HasOne(p => p.Company) .WithMany(t => t.Users) .OnDelete(DeleteBehavior.Cascade); > > public class Company < public int Id < get; set; >public string Name < get; set; >public List Users < get; set; >> public class User < public int Id < get; set; >public string Name < get; set; >public int? CompanyId < get; set; >public Company Company < get; set; >>

Соответственно чтобы отключить каскадное удаление, нам надо использовать вызов OnDelete(DeleteBehavior.SetNull) .

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 11.6: Каскадное удаления данных в базах данных SQLite

27.06.2016
SQLite библиотека, Базы данных
5 комментариев

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Прошлая публикация была про каскадную модификацию, в этой мы с вами поговорим про каскадное удаление данных из базы данных SQLite3. Каскадное удаление данных это способ удалить данные из связанных таблиц и при этом не нарушить ограничение внешнего ключа, кроме того, каскадное удаление данных упрощает нам работу при удалении строк в связанных таблицах.

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

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

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

Мы уже рассмотрели всевозможные способы обеспечения целостности данных в базах данных под управлением SQLite. Успели разобраться с ограничениями уровня столбца и ограничениями уровня таблицы и чуть ранее рассмотрели каскадное обновление данных. Теперь перейдем к каскадному удалению данных в базах данных SQLite3.

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

Чтобы избежать такой проблемы и не писать лишние SQL запросы в SQLite было реализовано каскадное удаление данных из таблиц, при каскадном удаление данных мы не пишем лишний SQL запрос DELETE, а также гарантируем себе то, что целостность данных в базах данных не будет нарушена.

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

Чтобы реализовать каскадное удаление данных из таблицы базы данных, нужно записать правило ON DELETE сразу после FOREIGN KEY, тогда SQLite начнет «понимать», что при удалении данных из справочника, ей нужно удалять и данные из связанных таблиц.

Давайте реализуем пример связи многие ко многим и посмотрим, как происходит каскадное удаление данных из базы данных SQLite3 и поймем, как работает конструкция ON DELETE CASCADE.

Примеры каскадного удаления данных из базе данных SQLite. ON DELETE в SQLite3

Чтобы посмотреть, как удалить данные каскадом, давайте создадим три таблицы в базе данных, которые будут связаны связью многие ко многим, воспользуемся командой CREATE:

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

Информация в статье относится к 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 это ключевое слово не используется.

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

mysql
sql
проектирование баз данных

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

Если в базе данных есть объекты типа Клиент , ссылающиеся на Адрес , то без дополнительных настроек при попытке удаления объекта типа Адрес произойдёт ошибка. База данных не даст удалить такой объект.

Варианты решения проблемы

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

Специальные интерфейсы

Для реализации каскадного удаления можно воспользоваться специально разработанными интерфейсами IReferencesCascadeDelete и IReferencesNullDelete.

Рекурсивное удаление

Это самый простой вариант, но и самый недружелюбный к пользователю: удаление 1 объекта может привести к удалению важной информации информации, связанной с данным объектом.

В бизнес-сервере мастера (в примере — Адрес ) вычитать все объекты, ссылающиеся на удаляемый.
Проставить всем объектам статус ObjectStatus.Deleted.
Отправить на удаление все объекты.
Повторить рекурсивно для всех объектов.

Фиктивный объект

Такой вариант позволяет сохранить все данные, кроме того объекта, который необходимо удалить. Однако в базе останется множество объектов, ссылающихся на несуществующий.

Стоит также отметить, что данный способ требует дополнительной обработки данных при выводе пользователю. Объекты, ссылающиеся на фиктивные, необходимо фильтровать или обрабатывать особым образом.

Вариантов решения проблемы несколько:

создавать фиктивный объект при каждом удалении
создать по 1 фиктивному объекту для каждого класса и “вешать” все ссылки на него.

Алгоритм для второго варианта:

(один раз) Создать объект и записать его в базу. Запоминить его PrimaryKey, например, в файле конфигурации или в файле с константами.
В бизнес-сервере мастера (в примере — Адрес ) вычитать все объекты, ссылающиеся на удаляемый.
Проставить всем объектам ссылку на фиктивный объект.
Отправить на обновление все объекты.

Фиктивное удаление

При фиктивном удалении данные на самом деле не удаляются из базы, а всего лишь помечаются как удаленные. Во все объекты добавляется какое-нибудь поле типа bool . При удалении объекта в бизнес-сервере перехватывается объект, у него меняется статус с Deleted на Altered и изменяется поле Актуально = false; .

После этого объект уходит на обновление в базу и остается в ней, но считается удаленным. Разумеется, необходимо реализовывать логику, которая будет “считать” такие объекты удаленными: при выводе информации пользователю необходимо накладывать ограничения на выводимые данные.

Note: Такой способ позволяет восстанавливать удаленные объекты.

Пример

Необходимо доработать диаграмму классов таким образом, чтобы она поддерживала фиктивное удаление: добавить поле Актуально:bool .

Добавить логику в бизнес-сервера объектов (на примере Адреса ):

if (UpdatedObject.GetStatus() == ObjectStatus.Deleted)  // Не дадим объекту удалиться, но выставим флаг Актуальности. UpdatedObject.SetStatus(ObjectStatus.Altered); UpdatedObject.Актуально = false; // Найдем все объекты, ссылающиеся на "удаляемый" и удалим их. var ds = (SQLDataService)DataServiceProvider.DataService; var klients = ds.QueryКлиент>(Клиент.Views.КлиентE) .Where(k => k.Прописка.__PrimaryKey == UpdatedObject.__PrimaryKey); foreach (var k in klients)  k.SetStatus(ObjectStatus.Deleted); > return klients.ToArray(); >

Note: Внимание! Cсылающиеся объекты отправленные на удаление, но они точно также перехватятся в своем бизнес-сервере и не удалятся.

Далее, чтобы пользователю не выводились “удаленные” данные при просмотре списка объектов, требуется на соответствующий контрол наложить ограничение вида:

var ds = (MSSQLDataService)DataServiceProvider.DataService; IQueryableКлиент> limit1 = ds.QueryАдрес>(Адрес.Views.АдресL).Where(Address => Address.Актуально); Function onlyActual = LinqToLcs.GetLcs(limit1.Expression, Адрес.Views.АдресL).LimitFunction;

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