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

[php]

PRAGMA foreign_keys=on;

CREATE TABLE books (

Id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

count_page INTEGER NOT NULL CHECK (count_page>0),

price REAL CHECK (price >0)

);

CREATE TABLE auth (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER  CHECK (age >16)

);

CREATE TABLE auth_book (

auth_id INTEGER NOT NULL,

books_id INTEGER NOT NULL,

FOREIGN KEY (auth_id) REFERENCES auth (id) ON DELETE CASCADE

FOREIGN KEY (books_id) REFERENCES books (id) ON DELETE CASCADE

);

[/php]

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

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

[php]

— Сперва добавим несколько строк в таблицу книг

INSERT INTO books (id, title, count_page, price)

VALUES (1, ‘Белыйклык’, 287, 300.00);

INSERT INTO books (id, title, count_page, price)

VALUES (2, ‘Война и мир’, 806, 780.00);

— Затемдобавимнесколькоавторов

INSERT INTO auth (id, name, age)

VALUES (1, ‘ДжекЛондон’, 40);

INSERT INTO auth (id, name, age)

VALUES (2, ‘Лев Толстой’, 82);

— Мы добавили книги и авторов из предыдущего примера

— Давайтедобавимновуюкнигу

INSERT INTO books (id, title, count_page, price)

VALUES (3, ’12 стульев’, 516, 480.00);

— И добавим авторов, так 12 стульев была написана в соавторстве

— Затемдобавимнесколькоавторов

INSERT INTO auth (id, name, age)

VALUES (3, ‘ИльяИльф’, 39);

INSERT INTO auth (id, name, age)

VALUES (4, ‘Евгений Петров’, 38);

— Связываем таблицы authи book

— для этого наполним данными результирующую таблицу

INSERT INTO auth_book (auth_id, books_id)

VALUES (1, 1);

INSERT INTO auth_book (auth_id, books_id)

VALUES (2, 2);

INSERT INTO auth_book (auth_id, books_id)

VALUES (3, 3);

INSERT INTO auth_book (auth_id, books_id)

VALUES (4, 3);

[/php]

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

DELETE FROM books WHERE id = 1;

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

[php]

sqlite> select * from books;

Id|title|count_page|price

2|Войнаимир|806|780.0

3|12 стульев|516|480.0

sqlite> select * from auth_book;

auth_id|books_id

2|2

3|3

4|3

sqlite> select * from auth;

id|name|age

1|ДжекЛондон|40

2|Лев Толстой|82

3|Илья Ильф|39

4|Евгений Петров|38

[/php]

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

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

[php]

DELETE FROM auth_book WHERE auth_id = 2;

[/php]

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

[php]

sqlite> select * from auth;

1|ДжекЛондон|40

2|Лев Толстой|82

3|Илья Ильф|39

4|Евгений Петров|38

sqlite> select * from books;

2|Войнаимир|806|780.0

3|12 стульев|516|480.0

sqlite> select * from auth_book;

3|3

4|3

[/php]

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

Возможно, эти записи вам покажутся интересными


Выберете удобный для себя способ, чтобы оставить комментарий

This article has 5 comments

  1. BDloCoder Reply

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

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

  2. Олечка Reply

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

    • Кирилл Reply

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

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

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

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

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

  3. Теман Reply

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

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

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

    • Кирилл Reply

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

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Loading Disqus Comments ...