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

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

Каскадная модификации данных в базах данных SQLite3

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

Каскадная модификация данных в базе данных SQLite. ON UPDATE в SQLite3

Обеспечение целостности данных – очень важная тема при рассмотрение любой СУБД, когда мы рассматривали ограничения уровня таблица, а точнее внешние ключи в базах данных SQLite3, то столкнулись с тем, что для модификации данных таблицы со ссылкой при помощи команды UPDATE, нам было необходимо сперва изменить данные в таблице-справочнике, а уже затем изменять данные в таблице, которая ссылается на справочник.

С этой проблемой нам поможет справиться каскадная модификация данных. Каскадная модификация данных в базах данных SQLite реализована при помощи ON UPDATE. Ключевая фраза ON UPDATE позволяет нам избавиться от сложностей модификации таблиц в базе данных, которые связаны между собой внешним ключом FOREIGN KEY.

Как понимать фразу каскадная модификация? Всё дело в том, что ключевая фраза ON UPDATE CASCADE, которая указывается после FOREIGEN KEY говорит СУБД о том, что та должна модифицировать обе таблицы друг за другом – каскадно. Таким образом, указав SQLite, что данные должны модфицироваться каскадом, мы сможем написать только один SQL запрос UPDATE, а SQLite сама обновит обе таблицы.

Каскадную модификацию ON UPDATE лучше показать на примере, так будете доступнее, понятнее и нагляднее.

Примеры каскадной модификации данных в базе данных SQLite. ON UPDATE в SQLite3

Давайте посмотрим пример каскадной модификации ON UPDATE в базах данных SQLite. Для этого создадим две таблицы, связанных внешним ключом, при помощи команды 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),

auth_id INTEGER NOT NULL,

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

);

CREATE TABLE auth (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER  CHECK (age >16)

);

[/php]

При создании таблиц с внешним ключом не забывайте о команде PRAGMA. Заметили конструкцию ON UPDATE CASCADE после ключевого слова REFERENCE? Данная конструкция говорит SQLite о том, что при обновление данных она должна обновить данные в обеих таблицах, тем самым мы избежим двух вещей: нам не нужно будет два раза использовать команду UPDATE и мы избежим нарушения ограничения внешнего ключа.

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

[php]

— Добавляем данные о авторах

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, auth_id)

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

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

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

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

VALUES (3, ‘Зов предков’, 121, 160.00, 1);

[/php]

Ничего нового мы не сделали, но теперь давайте попробуем модифицировать данные в таблицах командой UPDATE. Давайте заменим Льву Толстому значение id.

[php]

UPDATE auth SET id = 3 WHERE name = 'Лев Толстой';

[/php]

Мы можем проверить результаты модификации, сделав выборку данных из таблицы базы данных, для этого есть команда SELECT:

[php]

— Проверяем значение в таблице books

SELECT * FROM books;

— Проверяем значение в таблице auth

SELECT * FROM auth;

[/php]

Обратите внимание на результат:

[php]

sqlite> SELECT * FROM books;

Id      title   count_page      price   auth_id

1       Белый клык      287     300.0   1

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

3       Зов предков     121     160.0   1

sqlite> SELECT * FROM auth;

id      name    age

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

3       Лев Толстой     82

[/php]

Благодаря конструкции ON UPDATE CASCADE изменения в базу данных были внесены автоматически. Каскадная модификация данных позволяет вносить изменения в базу данных автоматически и при этом, не нарушая ограничение внешнего ключа.