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

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

 

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

Каскадное удаления данных в базах данных 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:

Не забываем включать поддержку внешних ключей в SQLite при помощи команды PRAGMA. Мы создали три таблицы: таблицу книг, таблицу авторов и результирующую таблицу, которая реализует связь многие ко многим. На ограничение первичного ключа или PRIMARY KEY сейчас не обращайте внимание, оно в данном случае нас не сильно интересует. SQLite теперь понимает, что данные нужно удалять каскадом, так как мы указали фразу ON DELETE CASCADE после ключевого слова REFERENCES.

Давайте теперь добавим строки в таблицы при помощи команды INSERT:

Мы создали три таблицы и за счет внешних ключей реализовали связи многие ко многими, сказали SQLite3 о том, что данные нужно удалять из таблиц каскадом при помощи ключевой фразы ON DELETE CASCADE, теперь, если мы попытаемся удалить данные из какой-либо таблицы, то данные из таблицы справочника будут так же удалены:

DELETE FROM books WHERE id = 1;

Проверим результаты каскадного удаления данных из базы данных, сделав выборку данных при помощи команды SELECT:

Обратите внимание: данные в таблице авторов остались. Каскадное удаление произошло только для промежуточной таблицы и таблицы книг. Данные в таблице авторов остались из-за того, что SQLite считает, что, возможно, мы будем использовать эти данные для дальнейшей работы.

Давайте попробуем удалить строку из таблицы справочника:

Данные будут удалены только из таблицы справочника:

Мы рассмотрели каскадное удаление данных из базы данных SQLite, которая реализуется при помощи ключевого слова ON DELETE CASCADE. Во-первых, каскадное удаление данных уменьшает количество SQL запросов. Во-вторых, конструкция ON DELETE CASADE позволяет удалять данные из таблицы не нарушая ограничение внешнего ключа.

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

BDloCoder

Базы данных SQLite и без того ориентированы на поддержание целостности данных и обожают инсерты из всех прочих запросов, так как каждая операция в базе данных — это минитранзакция, которая замаскирована под обычный запрос.

И действительно каскадное удаление данных нужно применять и применять правильно, что бы не дай Бог не нарушить структуру хранения данных, иначе долго придется возиться с бэкапами!

Олечка

Не совсем поняла, чем отличается каскадное удаление данных от обычного удаления, ведь и то и другое выполняется оператором DELETE, поясните?

Кирилл

Олечка, и каскадное удаление данных из базы данных и обычное удаление данных из базы данных действительно выполняется командой DELETE, но разница в том, что:

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

2. Каскадное удаление данных реализуется только в том случае, если оно задано для связанных столбцов конструкцией ON DELETE CASCADE.

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

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

Теман

Добрый день. В тексте статьи указано:

«сперва нужно удалить данные из таблицы справочника, а затем удалить строку из таблицы, которая ссылается на справочник».

Думается, что тут очепятка.

Кирилл

Согласен, спасибо, что заметили и указали на ошибку!

Текст комментария: