Для заданного внешнего ключа ограничение удаление каскадируется означает что

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

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

Привет, посетитель сайта 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:

Источник

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

у меня проблема, когда я пытаюсь добавить ограничения в свои таблицы. Я получаю сообщение об ошибке:

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

мне нужно, чтобы поля были установлены в null, если код, на который ссылаются, удален.

любые идеи, как я могу это сделать?

9 ответов

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

fwiw разрешение каскадных путей является сложной проблемой. Другой SQL продукты просто проигнорируют проблему и позволят вам создать циклы, в этом случае это будет гонка, чтобы увидеть, что перепишет значение последним, вероятно, к незнанию дизайнера (например, ACE/Jet делает это). Я понимаю, что некоторые продукты SQL попытаются разрешить простые случаи. Факт остается фактом, SQL Server даже не пытается, играет ультра безопасно, запрещая более одного пути, и, по крайней мере, он говорит вам об этом.

типичная ситуация с несколькими дорожками cascasing будет этого: Мастер-таблица с двумя деталями, скажем, «Master» и «Detail1″и » Detail2″. Обе детали-каскадное удаление. Пока никаких проблем. Но что, если обе детали имеют отношение «один ко многим»с какой-то другой таблицей (скажем, «SomeOtherTable»). SomeOtherTable имеет столбец Detail1ID и столбец Detail2ID.

другими словами: некоторые записи в SomeOtherTable связаны с Detail1-records и некоторыми из записи в SomeOtherTable связаны с записями Detail2. Даже если гарантируется, что записи SomeOtherTable никогда не принадлежат обеим деталям, теперь невозможно удалить каскад записей SomeOhterTable для обеих деталей, потому что существует несколько каскадных путей от Master к SomeOtherTable (один через Detail1 и один через Detail2). Возможно, вы уже поняли это. Вот возможное решение:

все ID поля ключевые поля и автоинкремент. Затруднение лежит в полях DetailMainId таблиц Detail. Эти поля являются как ключевыми, так и ссылочными. Теперь можно каскадно удалить все, только удалив мастер-записи. Недостатком является то, что для каждой записи detail1 и для каждой записи detail2 также должна быть запись DetailMain (которая фактически создается первой, чтобы получить правильный и уникальный идентификатор).

Я бы указал, что (функционально) существует большая разница между циклами и/или несколькими путями в схеме и данных. Хотя циклы и, возможно, многолучевости в данных, безусловно, могут усложнить обработку и вызвать проблемы с производительностью (стоимость «правильной» обработки), стоимость этих характеристик в схеме должна быть близка к нулю.

поскольку большинство очевидных циклов в RDBs происходят в иерархических структурах (орг-диаграмма, часть, подраздел и т. д.) к сожалению этот SQL Server предполагает худшее; т. е. цикл схемы = = цикл данных. Фактически, если вы используете ограничения RI, вы не можете построить цикл в данных!

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

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

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

Celko предлагает «лучший» способ представления иерархий, который не вводит циклы, но есть компромиссный.

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

по звукам у вас есть действие OnDelete/OnUpdate на одном из ваших существующих внешних ключей, которое изменит вашу таблицу кодов.

таким образом, создавая этот внешний ключ, вы создадите циклическую проблему,

например. Обновление сотрудников, приводит к изменению кодов действием On Update, приводит к изменению сотрудников действием On Update. так далее.

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

это потому, что Emplyee может иметь коллекцию других сущностей, скажем, квалификация и квалификация могут иметь некоторые другие университеты коллекции например,

на DataContext это может быть как ниже

в этом случае существует цепочка от сотрудника до квалификации и от квалификации до университетов. Таким образом, он бросал то же исключение мне.

это сработало для меня, когда я изменил

это ошибка типа политики триггера базы данных. триггер-это код и может добавить некоторые интеллекты или условия в каскадное отношение, такое как каскадное удаление. вам может потребоваться специализировать связанные параметры таблиц вокруг этого, как отключение CascadeOnDelete:

или полностью отключить эту функцию:

триггер является решением этой проблемы:

мое решение этой проблемы, возникшей с использованием ASP.NET Core 2.0 и EF Core 2.0 должны были выполнить следующее по порядку:

возьмите полученный скрипт и найдите ON DELETE CASCADE и заменить на ON DELETE NO ACTION

выполнить измененный SQL для базы данных

теперь ваши миграции должны быть актуальными, и каскадные удаления не должны происходить.

жаль, что я не смог найти способ сделать это в Entity Framework Core 2.0.

Источник

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

Дополнение к циклу переведенных статей.
Статьи: 1-3, 4-6, 7-9, 10-13, 14-15

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

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

Введение.

Ближе к сути.

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

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

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

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

Книга 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 это ключевое слово не используется.

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

Источник

Для заданного внешнего ключа ограничение удаление каскадируется означает что

Напомним, что ключ или возможный ключ – это минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать сущность по оставшимся. Каждая сущность обладает хотя бы одним возможным ключом. Один из них принимается за первичный ключ. При выборе первичного ключа следует отдавать предпочтение несоставным ключам или ключам, составленным из минимального числа атрибутов. Нецелесообразно также использовать ключи с длинными текстовыми значениями (предпочтительнее использовать целочисленные атрибуты). Так, для идентификации студента можно использовать либо уникальный номер зачетной книжки, либо набор из фамилии, имени, отчества, номера группы и может быть дополнительных атрибутов, так как не исключено появление в группе двух студентов (а чаще студенток) с одинаковыми фамилиями, именами и отчествами. Плохо также использовать в качестве ключа не номер блюда, а его название, например, » Закуска из плавленых сырков «Дружба» с ветчиной и соленым огурцом» или «Заяц в сметане с картофельными крокетами и салатом из красной капусты».

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

Теперь о внешних ключах:

В п. 2.3 рассматривался пример, где «Служащие» обозначали «Отделы» и включали внешний ключ «Номер отдела», соответствующий первичному ключу сущности «Отделы».

Связь между первичными и внешними ключами сущностей иллюстрируется рис. 2.5.

Здесь для обозначения любой из ассоциируемых сущностей (стержней, характеристик, обозначений или даже ассоциаций) используется новый обобщающий термин «Цель» или «Целевая сущность».

Таким образом, при рассмотрении проблемы выбора способа представления ассоциаций и обозначений в базе данных основной вопрос, на который следует получить ответ: «Каковы внешние ключи?». И далее, для каждого внешнего ключа необходимо решить три вопроса:

1. Может ли данный внешний ключ принимать неопределенные значения (NULL-значения)? Иначе говоря, может ли существовать некоторый экземпляр сущности данного типа, для которого неизвестна целевая сущность, указываемая внешним ключом? В случае поставок это, вероятно, невозможно – поставка, осуществляемая неизвестным поставщиком, или поставка неизвестного продукта не имеют смысла. Но в случае с сотрудниками такая ситуация однако могла бы иметь смысл – вполне возможно, что какой-либо сотрудник в данный момент не зачислен вообще ни в какой отдел. Заметим, что ответ на данный вопрос не зависит от прихоти проектировщика базы данных, а определяется фактическим образом действий, принятым в той части реального мира, которая должна быть представлена в рассматриваемой базе данных. Подобные замечания имеют отношение и к вопросам, обсуждаемым ниже.

2. Что должно случиться при попытке УДАЛЕНИЯ целевой сущности, на которую ссылается внешний ключ? Например, при удалении поставщика, который осуществил по крайней мере одну поставку. Существует три возможности:

3. Что должно происходить при попытке ОБНОВЛЕНИЯ первичного ключа целевой сущности, на которую ссылается некоторый внешний ключ? Например, может быть предпринята попытка обновить номер такого поставщика, для которого имеется по крайней мере одна соответствующая поставка. Этот случай для определенности снова рассмотрим подробнее. Имеются те же три возможности, как и при удалении:

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

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

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

Источник

Хорошее объяснение каскадного (ON DELETE / UPDATE) поведения

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

Само FOREIGN KEY ограничение также может быть описано как Allow No Orphans! (во-первых). Никогда не Child должно быть разрешено (записано) в дочерней таблице, если она не имеет Parent (строка в родительской таблице).

Для согласованности, ON DELETE RESTRICT можно перевести в (менее агрессивный) You Can’t Kill Parents! Только бездетные строки могут быть уничтожены (удалены.)

. это может означать удаление родительской записи при удалении дочерней записи, или это может означать удаление дочерней записи при удалении родительской записи. Так что это?

Один из способов интерпретации объявления внешнего ключа: «Все допустимые значения для этого столбца взяты из« that_column »в« that_table »». Когда вы удаляете строку в «дочерней» таблице, это никого не волнует. Это не влияет на целостность данных.

SQL: 2011 Spec

Пример / Метафора / Объяснение

Если все business они непосредственно затронуты их bourgeoisie путем, fatcat_owner то что вы делаете после рабочей революции, когда вы очищаете ее fatcat_owner и имеете бесклассовое общество?

У вас есть несколько вариантов здесь,

Позвольте этому продолжаться. Если так, когда произойдет революция, SQL дает вам четыре варианта,

Источник