Как сделать каскадное удаление sql
Обновлено: 07.07.2024
Я должен пропустить что-то тривиальное с параметрами каскада SQLAlchemy, потому что я не могу получить простое каскадное удаление для правильной работы-если родительский элемент удален, дети сохраняются, с null внешние ключи.
Я поставил краткий тестовый случай здесь:
есть простой, один-ко-многим отношения между родителем и ребенком. Скрипт создает родителя, добавляет 3 дочерних элемента, а затем фиксирует. Затем он удаляет родитель, но дети упорствуют. Зачем? Как сделать так, чтобы дети каскадно удалялись?
проблема в том, что sqlalchemy считает Child как родитель, потому что именно там вы определили свои отношения (его не волнует, что вы назвали его "ребенком", конечно).
если вы определяете отношение на Parent класс вместо этого, он будет работать:
(Примечание "Child" как строку: это позволило при использовании декларативного стиля, так что вы можете ссылаться на класс, который еще не определен)
вы можете добавить delete-orphan а ( delete вызывает удаление детей при удалении родителя, delete-orphan также удаляет все дочерние элементы, которые были "удалены" из родителя, даже если родитель не удален)
EDIT: только что узнал: если вы действительно хотите определить отношения на Child класс, вы можете сделать это, но вам придется определить каскад на значение по ссылке (путем создания явно значение по ссылке), вроде этого:
(подразумевая from sqlalchemy.orm import backref )
@Стивен-это хорошо, когда вы удаляете через session.delete() что никогда не происходит в моем случае. Я заметил, что большую часть времени я удаляю через session.query().filter().delete() (который не помещает элементы в память и удаляет непосредственно из БД). Используя этот метод с SQLAlchemy это cascade='all, delete' не работает. Есть решение, хотя: ON DELETE CASCADE через дБ (примечание: не все базы данных поддерживают его).
довольно старый пост, но я просто потратил час или два на это, поэтому я хотел поделиться своим открытием, тем более, что некоторые из других комментариев перечислены не совсем правильно.
TL; DR
дайте дочерней таблице иностранную или измените существующую, добавив onedelete='CASCADE' :
и один из следующих отношений:
а) это на родительской таблице:
б) или это на ребенка таблица:
подробности
во-первых, несмотря на то, что принятый ответ говорит, отношения родитель/ребенок не устанавливается с помощью relationship , это установлено с помощью ForeignKey . Вы можете поставить relationship на родительских или дочерних таблицах, и он будет работать нормально. Хотя, по-видимому, на дочерних таблицах вы должны использовать backref функция в дополнение к аргументу ключевого слова.
Вариант 1 (предпочтительно)
второй, SqlAlchemy поддерживает два различных вида каскадирования. Первый и тот, который я рекомендую, встроен в вашу базу данных и обычно принимает форму ограничения на объявление внешнего ключа. В PostgreSQL это выглядит так:
это означает, что при удалении записи из parent_table , то все соответствующие строки child_table будет удален для вас базой данных. Это быстро и надежно, и, вероятно, ваш лучший выбор. Вы устанавливаете это в SqlAlchemy через ForeignKey вот так (часть определения дочерней таблицы):
The ondelete='CASCADE' это часть, которая создает ON DELETE CASCADE на столе.
попался!
здесь есть важная оговорка. Обратите внимание, как у меня relationship указан с passive_deletes=True ? Если у вас нет этого, все это не будет работать. Это связано с тем, что по умолчанию при удалении родительской записи SqlAlchemy делает что-то действительно странное. Он устанавливает внешние ключи всех дочерних строк NULL . Так что если вы удаляете строку из parent_table здесь id = 5, то он будет в основном выполнять
зачем вам это нужно, я понятия не имею. Я был бы удивлен, если бы многие ядра баз данных даже позволили Вам установить действительный внешний ключ в NULL , создавая сиротой. Кажется, это плохая идея, но, возможно, есть прецедент. В любом случае, если вы позволите SqlAlchemy сделать это, вы не сможете очистить базу данных от детей с помощью ON DELETE CASCADE что вы создали. Это потому, что он полагается на эти внешние ключи, чтобы знать, какие дочерние строки для удаления. После того, как SqlAlchemy установил их все в NULL база данных не можете удалить их. Установка passive_deletes=True предотвращает SqlAlchemy от NULL ing из внешних ключей.
вы можете прочитать больше о пассивных удалениях в SQLAlchemy docs.
2другой способ сделать это-позволить SqlAlchemy сделать это за вас. Это настраивается с помощью
Стивен прав в том, что вам нужно явно создать backref, это приводит к тому, что каскад применяется к родителю (в отличие от его применения к ребенку, как в тестовом сценарии).
однако определение отношения на дочернем элементе не делает sqlalchemy считать дочерний элемент родительским. Не имеет значения, где определяется отношение (дочерний или родительский), его внешний ключ, который связывает две таблицы, определяющие, какой из них является родительским, а какой - ребенок.
имеет смысл придерживаться одного соглашения, хотя, и основываясь на ответе Стивена, я определяю все свои дочерние отношения на родителе.
Я также боролся с документацией, но обнаружил, что сами документы, как правило, проще, чем руководство. Например, если вы импортируете отношения из sqlalchemy.orm и do help (relationship), он даст вам все параметры, которые вы можете указать для cascade. Пуля для " delete-orphan "говорит:" Если обнаружен элемент типа ребенка без родителя, отметьте его для удаления. Обратите внимание, что этот параметр предотвращает сохранение ожидающего элемента дочернего класса без родитель присутствует."
Я понимаю, что ваша проблема была больше с тем, как документация для определения отношений родитель-ребенок. Но казалось, что у вас также могут возникнуть проблемы с параметрами каскада, потому что "все" включает "удалить".""удалить сироту" - это единственный вариант, который не включен в "все"."
ответ Стивена твердый. Я хотел бы указать на дополнительный подтекст.
С помощью relationship , вы делаете слой приложения (колбу) ответственным за ссылочную целостность. Это означает, что другие процессы, которые обращаются к базе данных не через Flask, такие как утилита базы данных или человек, подключающийся к базе данных напрямую, не будут испытывать этих ограничений и могут изменить ваши данные таким образом, чтобы сломать логическую модель данных, над которой вы так усердно работали дизайн.
по возможности используйте ForeignKey подход, описанный d512 и Алекс. Движок БД очень хорошо справляется с действительно принудительными ограничениями (неизбежным образом), поэтому это, безусловно, лучшая стратегия для поддержания целостности данных. Единственный раз, когда вам нужно полагаться на приложение для обработки целостности данных, когда база данных не может их обрабатывать, например версии SQLite, которые не поддерживают внешние ключи.
Если вам нужно создать дополнительную связь между объектами, чтобы включить приложение поведения, такие как навигация по отношениям между родительскими и дочерними объектами, используйте backref в сочетании с ForeignKey .
Внешний ключ с каскадным удалением означает, что если запись в родительской таблице будет удалена, то соответствующие записи в дочерней таблице будут автоматически удалены. Это называется каскадным удалением в SQL Server.
Внешний ключ с каскадным удалением может быть создан с использованием оператора CREATE TABLE или оператора ALTER TABLE.
Создание внешнего ключа с каскадным удалением — использование оператора CREATE TABLE
Синтаксис
Синтаксис создания внешнего ключа с каскадным удалением с использованием оператора CREATE TABLE в SQL Server (Transact-SQL):
CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, . child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, . parent_col_n)
ON DELETE CASCADE
[ ON UPDATE ]
);
child_table — имя дочерней таблицы, которую вы хотите создать.
column1 , column2 — столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.
fk_name — имя ограничения внешнего ключа, которое вы хотите создать.
child_col1 , child_col2 , . child_col_n — столбцы в child_table , которые будут ссылаться на первичный ключ в parent_table (родительской таблице).
parent_table — имя родительской таблицы, первичный ключ которой будет использоваться в child_table .
parent_col1 , parent_col2 , . parent_col3 — столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1 , child_col2 , . child_col_n в child_table .
ON DELETE CASCADE — указывает, что дочерние данные удаляются при удалении родительских данных.
ON UPDATE — необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
NO ACTION — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.
CASCADE — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.
SET NULL — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.
SET DEFAULT — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.
Пример
Давайте рассмотрим пример создания внешнего ключа с каскадным удалением в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE.
Например:
Каскадное удаление данных
в мире реляционных баз данных позволяет удалять связанные данные из зависимой таблицы, при удалении данных из основной таблицы. В случае модели, которую мы использовали в предыдущих примерах (две связанные таблицы 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 к навигационному свойству модели.
В этой форме используются две кнопки для удаления и сохранения данных. В коде обработчика 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) не поддерживают несколько отношений, которые определяют каскадное удаление, указываемое на одной таблице.
Русские Блоги
Как использовать каскадную для удаления в SQL Server?
I have 2 tables: T1 and T2, they are existing tables with data. У меня есть 2 таблицы: T1 и T2, они являются существующие таблицы с данными. We have a one to many relationship between T1 and T2. Существует пара отношений между T1 и T2. How do I alter the table definitions to perform cascading delete in SQL Server when a record from T1 is deleted, all associated records in T2 also deleted. Когда запись в T1 удаляются, как изменить определение таблицы для выполнения уровня в SQL Server, все связанные записи в T2 также удаляются.
The foreign constraint is in place between them. Существует внешнее ограничение между ними. I don’t want to drop the tables or create a trigger to do the deletion for T2. Я не хочу, чтобы удалить таблицу или создать триггер для удаления Т2. For example, when I delete an employee, all the review record should be gone, too. Например, при удалении сотрудника, все записи по рассмотрению должны также исчезнуть.
T1 — Employee, T1 — сотрудник,
T2 — Performance Reviews, T2 — Эффект Обзор,
First To Enable ONCascade property: Во-первых, вы хотите, чтобы включить oncascade свойства:
1.Drop the existing foreign key constraint 1. Отбросить существующие внешние ограничения ключевых
2.add a new one with the ON DELETE CASCADE setting enabled 2. Добавьте новый параметр включен каскад
Ex: Например:
Second To Disable ONCascade property: Второе Отключить oncascade свойства:
1.Drop the existing foreign key constraint 1. Отбросить существующие внешние ограничения ключевых
2.Add a new one with the ON DELETE NO ACTION setting enabled 2. Добавить новый Разблокирован Нет настройки действий
Ex: Например:
You can do this with SQL Server Management Studio. Вы можете сделать это с помощью SQL Server Management Studio.
Then, just add ON DELETE CASCADE to the ADD CONSTRAINT command: Тогда просто необходимо ON DELETE CASCADE добавить в ADD CONSTRAINT Порядок:
And if you ever find that you can’t DROP a particular table due to a Foreign Key constraint, but you can’t work out which FK is causing the problem, then you can run this command: И, если вы обнаружили, что из-за ограничения внешнего ключа DROP Конкретная таблица, но не может определить, какой FK является проблемой, вы можете выполнить следующую команду:
The SQL in that article lists all FKs which reference a particular table. SQL в этой статье перечислены все FKs, что ссылки конкретных таблиц.
Hope all this helps. Я надеюсь, что все это поможет вам.
Apologies for the long finger. Извиняюсь за длинные пальцы. I was just trying to make a point. Я просто хочу отметить немного.
Я думаю, вы не можете просто удалить свойство таблицы, если это фактические данные о производстве, просто удалите содержимое, которое не влияет на режиме таблицы.
ON DELETE CASCADE
It specifies that the child data is deleted when the parent data is deleted. Он определяет подменю Удалить данные при удалении родительских данных.
For this foreign key, we have specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted. Мы указали на эту кнопку. ON DELETE CASCADE Условие указывает SQL Server, чтобы удалить соответствующую запись в подразделе при удалении данных в родительской таблице. So in this example, if a product_id value is deleted from the products table, the corresponding records in the inventory table that use this product_id will also be deleted. Таким образом, в этом примере, если product_id значения удаляется из таблицы продукта, соответствующая запись используется в таблице инвентаризации также будет удалена.
Интеллектуальная рекомендация
Разработка Android NDK (1)
Подтверждение: я прошел настоящий тест. Кстати, многие люди в интернете не тестировали случайные волосы. Структура файлового каталога: app / src / main / java / com / hd / jni 1. Определите интерфейс .
у меня есть ограничение внешнего ключа в моей таблице, я хочу добавить к нему DELETE CASCADE.
Я попытался это:
EDIT:
Внешний ключ уже существует, есть данные в столбце внешнего ключа.
вы не можете добавить ON DELETE CASCADE к уже существующему ограничению. Вам придется drop и re - create ограничения. The документация видно, что MODIFY CONSTRAINT предложение может изменять только государственные ограничения (i-e: ENABLED/DISABLED . ).
первый drop ваш внешний ключ и попробуйте свою команду выше, put add constraint вместо modify constraint . Теперь это команда:
этот PL * SQL будет записывать в DBMS_OUTPUT скрипт, который удалит каждое ограничение, которое не имеет delete cascade, и воссоздаст его с помощью delete cascade.
Примечание: запуск вывода этого скрипта на свой страх и риск. Лучше всего прочитать полученный скрипт и отредактировать его перед выполнением.
Как уже было сказано:
Как вы можете видеть, они должны быть разделены командами, сначала отбрасывая, а затем добавляя.
для тех, кто использует MySQL:
если вы направляетесь в свой PHPMYADMIN веб-страницу и перейдите в таблицу, которая имеет внешний ключ, который вы хотите обновить, все, что вам нужно сделать, это нажать Relational view расположенном в Structure tab и изменить On delete выберите пункт Cascade .
изображение показано ниже:
вот удобное решение! Я использую SQL Server 2008 R2.
Если вы хотите изменить ограничение FK, добавив каскад удаления/обновления, выполните следующие действия:
номер 1:
щелкните правой кнопкой мыши на ограничении и выберите изменить
номер 2:
выберите ограничение с левой стороны (если их несколько). Затем на правой стороне, свернуть "спецификация вставки и обновления" укажите и укажите действия по удалению правила или обновлению строки правила в соответствии с вашими потребностями. После этого закройте диалоговое окно.
номер 3:
последним шагом является сохранение изменений тезисов (конечно!)
PS: это спасло меня от кучи работы, поскольку я хочу изменить первичный ключ, на который ссылается другой таблица.
Используйте инструкцию ALTER TABLE в следующих целях:
добавление или удаление ограничения без изменения структуры;
включение или отключение ограничений;
добавление ограничения NOT NULL с помощью предложения MODIFY .
Для существующих таблиц можно добавить ограничение, используя инструкцию ALTER TABLE с предложением ADD .
table имя таблицы;
constraint имя ограничения;
type тип ограничения;
column имя столбца, на который распространяется ограничение.
Синтаксис имени ограничения является необязательным, хотя рекомендуется его придерживаться. Если ограничениям не присваивать имена, имена ограничений создаются системой.
Ограничение можно добавить, удалить, включить или отключить, однако невозможно изменить структуру ограничения.
В существующий столбец можно добавить ограничение NOT NULL , используя предложение MODIFY инструкции ALTER TABLE .
Примечание . Столбец NOT NULL можно определить только в том случае, если таблица является пустой или если в столбце имеется значение для каждой строки.
Добавление ограничения
Добавьте в таблицу EMP2 ограничение внешнего ключа ( FOREIGN KEY ), указывающее, что менеджер уже должен существовать в таблице EMP2 как допустимый сотрудник.
В первом примере на рисунке изменяется таблица EMP2 для добавления ограничения первичного ключа ( PRIMARY KEY ) на столбец EMPLOYEE_ID . Обратите внимание, что поскольку имя ограничения не указано, ограничение автоматически именуется сервером Oracle. Во втором примере на рисунке создается ограничение внешнего ключа ( FOREIGN KEY ) на таблицу EMP2 . Ограничение гарантирует, что менеджер будет существовать в таблице EMP2 как допустимый сотрудник.
Параметр ON DELETE CASCADE
Удаляет дочерние строки при удалении родительского ключа:
Действие ON DELETE CASCADE позволяет удалять, но не обновлять данные родительского ключа, на которые имеются ссылки из дочерней таблицы. Когда данные в родительском ключе удаляются, все строки в дочерней таблице, которые зависят от значений удаленного родительского ключа, также удаляются. Чтобы задать это ссылочное действие, включите параметр ON DELETE CASCADE в определение ограничения FOREIGN KEY .
Читайте также: