Использование внешних ключей в MySQL
(...) Внешние ключи - это как раз то, что делает реляционные базы “реляционными” (от relation(англ.)- отношение, связь). Это как раз те связующие цепочки, которые связывают таблицы между собой. Они позволяют вам разместить “покупателей” в одной таблице, “заказы” в другой, а товары из этих заказов, в третьей, таким образом в базе минимизируется избыточность данных. Чем меньше избыточных данных - тем больше у вас шансов сохранить целостность данных (две или более противоречащие друг-другу записи - это всегда плохо).
Пример
Самое время продемонстрировать все на практике.
Этот пример написан для MySql, если вы используете другую СУБД, прочитайте сперва документацию по ней.
Этот блок SQL содержит исходный код создания таблиц для нашего примера:
| T-SQL |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| CREATE TABLE usr (
usr_id int AUTO_INCREMENT NOT NULL,
first varchar(25) NOT NULL,
surname varchar(50) NOT NULL,
PRIMARY KEY(usr_id)
) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE product (
prod_id int AUTO_INCREMENT NOT NULL,
name varchar(40) NOT NULL,
descr varchar(255) NOT NULL,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE invoice (
inv_id int AUTO_INCREMENT NOT NULL,
usr_id int NOT NULL,
prod_id int NOT NULL,
quantity int NOT NULL,
PRIMARY KEY(inv_id)
) ENGINE=InnoDB CHARACTER SET=UTF8; |
|
В таком виде, как это представлено сейчас, таблицы не связаны между собой, кроме как через названия колонок.
Могли бы мы хранить всю информацию в одной таблице? Конечно! Можно было бы создать таблицу, которая содержала бы в себе всю информацию о
покупателе, данные о товаре и количество заказанных товаров. Но что случится, если мы допустим ошибку в одной из записей в описании товара или имени покупателя?
О, нет, теперь все испорчено! Получим ли мы записи с опечатками вместе с другими записями? Что если нет? У нас некорректные данные - небеса рухнули.
Добавляем внешние ключи
Теперь пришло время установить связи между таблицами, которых не хватало до сих пор. Соединим колонки usr_id и prod_id таблицы
invoice с их соответствиями в таблицах usr и product.
Вы можете выполнить команду ALTER для таблицы invoice, но я предпочитаю вносить такие изменения на этапе проектирования, поэтому
можно внести изменения прямо запрос создания таблицы:
| T-SQL |
1
2
3
4
5
6
7
8
9
| CREATE TABLE invoice (
inv_id int AUTO_INCREMENT NOT NULL,
usr_id int NOT NULL,
prod_id int NOT NULL,
quantity int NOT NULL,
PRIMARY KEY(inv_id),
FOREIGN KEY (usr_id) REFERENCES usr(usr_id),
FOREIGN KEY (prod_id) REFERENCES product(prod_id)
) ENGINE=InnoDB CHARACTER SET=UTF8; |
|
таблицы с внешними ключами
Заметьте, что в новом варианте запроса CREATE для таблицы invoice, я добавил синтаксис FOREIGN KEY () REFERENCES table()
чтобы установить связь между таблицами.
Просто добавив объявления внешних ключей, мы добились встроенной защиты целостности данных.
Если мы попытаемся выполнить запрос INSERT или UPDATE со значением внешнего ключа для таблицы invoice, база данных автоматически проверит
существует ли данное значение в связанной таблице. Если указанных значений в связанных таблицах не существует - база данных не выполнит запрос INSERT/UPDATE,
сохранив таким образом целостность данных.
Теперь не придется проверять вручную родительскую таблицу на существование конкретных значений, прежде чем вставить данные в таблицу-потомок.
Также можете спокойно удалять записи. Хотите избежать ошибок новым способом? Меньше кодирования - лучший способ для ленивых программистов.
Поддадим газку
Готовы улучшить ваши внешние ключи еще? Да, они могу сделать значительно больше для вас.
На данный момент, у нас есть защита целостности данных на случай каких-либо манипуляций с таблицами-потомками, но что если внести изменения в родительскую таблицу?
Как нам быть уверенными, что таблицы-потомки в курсе всех изменений в родительской таблице?
MySQL позволяет нам контролировать таблицы-потомки во время обновления или удаления данных в родительской таблице с помощью подвыражений: ON UPDATE и ON DELETE.
MySQL поддерживает 5 действий, которые можно использовать в выражениях ON UPDATE и/или ON DELETE.
* CASCADE: если связанная запись родительской таблицы обновлена или удалена, и мы хотим чтобы соответствующие записи в таблицах-потомках также были обновлены
или удалены. Что происходит с записью в родительской таблице, тоже самое произойдет с записью в дочерних таблицах. Однако не забывайте, что здесь можно легко попасться в ловушку
бесконечного цикла.
* SET NULL:если запись в родительской таблице обновлена или удалена, а мы хоти чтобы в дочерней таблице некоторым занчениям было присвоено NULL (конечно если поле таблицы это позволяет)
* NO ACTION: смотри RESTRICT
* RESTRICT:если связанные записи родительской таблицы обновляются или удаляются со значениями которые уже/еще содержатся в соответствующих записях дочерней таблицы,
то база данных не позволит изменять записи в родительской таблице. Обе команды NO ACTION и RESTRICT эквивалентны отсутствию
подвыражений ON UPDATE or ON DELETE для внешних ключей.
* SET DEFAULT:На данный момент эта команда распознается парсером, но движок InnoDB никак на нее не реагирует.
Для моей базы данных из примера, я решил, что для внешних ключей из таблицы invoice, UPDATE будут выполняться каскадно для дочерних таблиц, а удаление будет запрещено.
Таким образом, любые изменения в таблицах usr и product автоматически отразятся в таблице invoice, но если товар заказан или если у пользователя есть счет - они не могут быть удалены.
Ниже представлен новый вариант запроса CREATE для таблицы invoice с внешними ключами и выражениями ON UPDATE и ON DELETE
| T-SQL |
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE TABLE invoice (
inv_id int AUTO_INCREMENT NOT NULL,
usr_id int NOT NULL,
prod_id int NOT NULL,
quantity int NOT NULL,
PRIMARY KEY(inv_id),
FOREIGN KEY (usr_id) REFERENCES usr(usr_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (prod_id) REFERENCES product(prod_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB CHARACTER SET=UTF8; |
|
Как вы смогли убедится, использовать внешние ключи достаточно просто. Перенеся эту логику в базу данных из кода мы значительно упрощаем себе жизнь.
Теперь нет необходимости писать и отлаживать ненужный код, что дает нам время поболтать лишние минуты по аське.
(
Сообщение от sigmaalgebra
