Часть 7.1: Команда SELECT в SQLite3. Оператор SELECT в SQLite3
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Первый…
Часть 11.5: Каскадная модификации данных в базах данных 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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) ); |
При создании таблиц с внешним ключом не забывайте о команде PRAGMA. Заметили конструкцию ON UPDATE CASCADE после ключевого слова REFERENCE? Данная конструкция говорит SQLite о том, что при обновление данных она должна обновить данные в обеих таблицах, тем самым мы избежим двух вещей: нам не нужно будет два раза использовать команду UPDATE и мы избежим нарушения ограничения внешнего ключа.
Давайте добавим несколько строк в таблицы базы данных командой INSERT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Добавляем данные о авторах 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); |
Ничего нового мы не сделали, но теперь давайте попробуем модифицировать данные в таблицах командой UPDATE. Давайте заменим Льву Толстому значение id.
|
1 |
UPDATE auth SET id = 3 WHERE name = 'Лев Толстой'; |
Мы можем проверить результаты модификации, сделав выборку данных из таблицы базы данных, для этого есть команда SELECT:
|
1 2 3 4 5 6 7 |
-- Проверяем значение в таблице books SELECT * FROM books; -- Проверяем значение в таблице auth SELECT * FROM auth; |
Обратите внимание на результат:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
Благодаря конструкции ON UPDATE CASCADE изменения в базу данных были внесены автоматически. Каскадная модификация данных позволяет вносить изменения в базу данных автоматически и при этом, не нарушая ограничение внешнего ключа.
Pingback: Космический Стас