My name is Vasyl Khrystiuk‎ > ‎process‎ > ‎main‎ > ‎

6. Зовнішні ключі

http://denis-in-ua.livejournal.com/7672.html

Использование внешних ключей в MySQL

Originally published at Denis.in.ua. You can comment here or there.

Нет, внешние ключи (foreign keys) на самом деле не из Бразилии или Италии, и даже не из США. Для тех кто не в теме, они могут показаться странными. Но не бойтесь, мы здесь как раз для того, чтобы научить вас как с ними обращаться. Итак, что собой представляют внешние ключи?

Внешние ключи - это как раз то, что делает реляционные базы “реляционными” (от relation(англ.)- отношение, связь). Это как раз те связующие цепочки, которые связывают таблицы между собой. Они позволяют вам разместить “покупателей” в одной таблице, “заказы” в другой, а товары из этих заказов, в третьей, таким образом в базе минимизируется избыточность данных. Чем меньше избыточных данных - тем больше у вас шансов сохранить целостность данных (две или более противоречащие друг-другу записи - это всегда плохо).

Пример

Самое время продемонстрировать все на практике.
Этот пример написан для MySql, если вы используете другую СУБД, прочитайте сперва документацию по ней.
Этот блок SQL содержит исходный код создания таблиц для нашего примера:

  1. CREATE TABLE usr (
  2.         usr_id  int AUTO_INCREMENT NOT NULL,
  3.         first  varchar(25) NOT NULL,
  4.            surname  varchar(50) NOT NULL,
  5.            PRIMARY KEY(usr_id)
  6.          ) ENGINE=InnoDB CHARACTER SET=UTF8;
  7.       CREATE TABLE product (
  8.            prod_id  int AUTO_INCREMENT NOT NULL,
  9.           name  varchar(40) NOT NULL,
  10.           descr  varchar(255) NOT NULL,
  11.           PRIMARY KEY(prod_id)
  12.         ) ENGINE=InnoDB CHARACTER SET=UTF8;
  13.  
  14.       CREATE TABLE invoice (
  15.           inv_id  int AUTO_INCREMENT NOT NULL,
  16.           usr_id  int NOT NULL,
  17.           prod_id  int NOT NULL,
  18.           quantity int NOT NULL,
  19.           PRIMARY KEY(inv_id)
  20.         ) ENGINE=InnoDB CHARACTER SET=UTF8;

А на это Диаграммы Отношений, для этих таблиц:
db_phase1.png

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

Могли бы мы хранить всю информацию в одной таблице? Конечно! Можно было бы создать таблицу, которая содержала бы в себе всю информацию о
покупателе, данные о товаре и количество заказанных товаров. Но что случится, если мы допустим ошибку в одной из записей в описании товара или имени покупателя?
О, нет, теперь все испорчено! Получим ли мы записи с опечатками вместе с другими записями? Что если нет? У нас некорректные данные - небеса рухнули.

Добавляем внешние ключи

Теперь пришло время установить связи между таблицами, которых не хватало до сих пор. Соединим колонки usr_id и prod_id таблицы
invoice с их соответствиями в таблицах usr и product.

Вы можете выполнить команду ALTER для таблицы invoice, но я предпочитаю вносить такие изменения на этапе проектирования, поэтому
можно внести изменения прямо запрос создания таблицы:

  1. CREATE TABLE invoice (
  2.     inv_id  int AUTO_INCREMENT NOT NULL,
  3.     usr_id  int NOT NULL,
  4.     prod_id  int NOT NULL,
  5.     quantity int NOT NULL,
  6.     PRIMARY KEY(inv_id),
  7.     FOREIGN KEY (usr_id) REFERENCES usr(usr_id),
  8.     FOREIGN KEY (prod_id) REFERENCES product(prod_id)
  9.   ) 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

  1. CREATE TABLE invoice (
  2.         inv_id  int AUTO_INCREMENT NOT NULL,
  3.         usr_id  int NOT NULL,
  4.         prod_id  int NOT NULL,
  5.         quantity int NOT NULL,
  6.         PRIMARY KEY(inv_id),
  7.         FOREIGN KEY (usr_id) REFERENCES usr(usr_id)
  8.           ON UPDATE CASCADE
  9.           ON DELETE RESTRICT,
  10.         FOREIGN KEY (prod_id) REFERENCES product(prod_id)
  11.           ON UPDATE CASCADE
  12.           ON DELETE RESTRICT
  13.       ) ENGINE=InnoDB CHARACTER SET=UTF8;

Выводы

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


//------------------------------------------------------------------------------------------------------------------------------------------------------------------------

добавлю від себе:

не забувати писати в кінці таблиці  

ENGINE=InnoDB;

або:

TYPE=InnoDB;

Comments