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

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

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

Пример

Самое время продемонстрировать все на практике. Этот пример написан для MySql, если вы используете другую СУБД, прочитайте сперва документацию по ней. Этот блок 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;

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

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

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

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

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

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

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.

Для моей базы данных из примера, я решил, что для внешних ключей из таблицы invoice, UPDATE будут выполняться каскадно для дочерних таблиц, а удаление будет запрещено. Таким образом, любые изменения в таблицах usr и product автоматически отразятся в таблице invoice, но если товар заказан или если у пользователя есть счет - они не могут быть удалены.

Ниже представлен новый вариант запроса CREATE для таблицы invoice с внешними ключами и выражениями ON UPDATE и ON DELETE

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;

Выводы

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

Вольный перевод: Using Foreign Keys in MySQL

2.Апр.2008 | В категории Базы данных

 

Комментарии

7 Комментариев к “Использование внешних ключей в MySQL”

  1. Владимир 2.Апр.2008 в 4:00 пп

    Хорошая статья.
    Если можно, небольшой вопросик: в какой программе нарисована диаграмма?

  2. Денис Солошенко 2.Апр.2008 в 4:44 пп

    MySQL’s Workbench. You can find it at http://dev.mysql.com/downloads/workbench/5.0.html

  3. Дима Христов 3.Апр.2008 в 11:19 пп

    небольшое уточнение, базы назвали relational вовсе не из-за того что существуют связи (relations) между разными таблицами, а из-за того что в одной отдельной таблице хранятся данные которые логически связаны между собой (are related).

  4. Денис Солошенко 4.Апр.2008 в 1:25 дп

    2 Дима Христов: спасибо, подправил.

  5. Артём Курапов 7.Апр.2008 в 5:51 пп

    Неплохо бы отметить что FK добавляются на движке InnoDB, в котором медленней count и полнотекстовый поиск.
    А так - ForeignKeys это сила, заменяет кучу кода.

  6. Денис Солошенко 7.Апр.2008 в 6:04 пп

    2 Артём Курапов: Count медленней только если в запросе Where не используется. Да и вообще я думаю оно того стоит. Вопросы производительности все-таки наверное менее приоритетны, чем вопросы хорошей архитектуры и целостности данных. ИМХО.

    Про движок добавлю, спасибо.

  7. hayk 18.Июн.2008 в 4:52 пп

    >MySQL’s Workbench
    Хорший тул, но данной версии есть один существенный недостаток - в нем нельзя печатать! Это пдц.

    P.S. Добавь плиз в блог возможность древовидных комментариев и авторизацию через openid.