Часть 12.3: Группировка данных выборки: GROUP BY и SELECT в SQLite
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Реляционные…
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. На своём блоге я уже довольно много писал про обеспечение целостности данных в реляционных базах данных и для примера мы используем замечательную встраиваемую библиотеку SQLite. Замечу, что многое из того, что написано актуально и для других серверов баз данных. Впрочем, с темой поддержания целостности данных мы почти закончили. Перейдем к рассмотрению каскадных операций манипуляции данными в SQLite. В базах данных SQLite таких операции реализовано две: каскадная модификация данных и каскадное удаление данных. Из этой публикации вы узнаете про каскадную модификацию данных в базах данных под управлением SQLite3.
Каскадная модификация данных позволяет избежать нарушения правила внешнего ключа, с помощью которого мы реализуем связи между таблицами в базе данных. Помимо этого, каскадная модификация уменьшает количество SQL запросов, необходимых для внесения изменения значений, которые хранятся в связанных таблицах. Сперва мы поговорим о реализации каскадной модификации данных в SQLite3, а затем рассмотрим пример каскадной модификации данных в базах данных.
Содержание статьи:
Обеспечение целостности данных – очень важная тема при рассмотрение любой СУБД, когда мы рассматривали ограничения уровня таблица, а точнее внешние ключи в базах данных SQLite3, то столкнулись с тем, что для модификации данных таблицы со ссылкой при помощи команды UPDATE, нам было необходимо сперва изменить данные в таблице-справочнике, а уже затем изменять данные в таблице, которая ссылается на справочник.
С этой проблемой нам поможет справиться каскадная модификация данных. Каскадная модификация данных в базах данных SQLite реализована при помощи ON UPDATE. Ключевая фраза ON UPDATE позволяет нам избавиться от сложностей модификации таблиц в базе данных, которые связаны между собой внешним ключом FOREIGN KEY.
Как понимать фразу каскадная модификация? Всё дело в том, что ключевая фраза ON UPDATE CASCADE, которая указывается после FOREIGEN KEY говорит СУБД о том, что та должна модифицировать обе таблицы друг за другом – каскадно. Таким образом, указав SQLite, что данные должны модфицироваться каскадом, мы сможем написать только один SQL запрос UPDATE, а SQLite сама обновит обе таблицы.
Каскадную модификацию ON UPDATE лучше показать на примере, так будете доступнее, понятнее и нагляднее.
Давайте посмотрим пример каскадной модификации 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 изменения в базу данных были внесены автоматически. Каскадная модификация данных позволяет вносить изменения в базу данных автоматически и при этом, не нарушая ограничение внешнего ключа.
Pingback: Космический Стас