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

Для начала давайте подумаем над таким вопросом: какую информацию нужно дать о человеке, чтобы собеседник точно сказал, что это именно тот человек, сомнений быть не может, второго такого нет? Сообщить фамилию, очевидно, недостаточно, поскольку существуют однофамильцы. Если собеседник человек, то мы можем приблизительно объяснить, о ком речь, например вспомнить поступок, который совершил тот человек, или еще как-то. Компьютер же такого объяснения не поймет, ему нужны четкие правила, как определить, о ком идет речь. В системах управления базами данных для решения такой задачи ввели понятие первичного ключа.

Первичный ключ (primary key, PK) – минимальный набор полей, уникально идентифицирующий запись в таблице. Значит, первичный ключ – это в первую очередь набор полей таблицы, во-вторых, каждый набор значений этих полей должен определять единственную запись (строку) в таблице и, в-третьих, этот набор полей должен быть минимальным из всех обладающих таким же свойством.

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

Например, в нашей таблице (см. выше) ФИО и адрес позволяют однозначно выделить запись о человеке. Если же говорить в общем, без связи с решаемой задачей, то такие знания не позволяют точно указать на единственного человека, поскольку существуют однофамильцы, живущие в разных городах по одному адресу. Все дело в границах, которые мы сами себе задаем. Если считаем, что знания ФИО, телефона и адреса без указания города для наших целей достаточно, то все замечательно, тогда поля ФИО и адрес могут образовывать первичный ключ. В любом случае проблема создания первичного ключа ложится на плечи того, кто проектирует базу данных (разрабатывает структуру хранения данных). Решением этой проблемы может стать либо выделение характеристик, которые естественным образом определяют запись в таблице (задание так называемого логического, или естественного, PK), либо создание дополнительного поля, предназначенного именно для однозначной идентификации записей в таблице (задание так называемого суррогатного, или искусственного, PK).

Примером логического первичного ключа является номер паспорта в базе данных о паспортных данных жителей или ФИО и адрес в телефонной книге (таблица выше). Для задания суррогатного первичного ключа в нашу таблицу можно добавить поле id (идентификатор), значением которого будет целое число, уникальное для каждой строки таблицы. Использование таких суррогатных ключей имеет смысл, если естественный первичный ключ представляет собой большой набор полей или его выделение нетривиально.

Кроме однозначной идентификации записи, первичные ключи используются для организации связей с другими таблицами.

Например, у нас есть три таблицы: содержащая информацию об исторических личностях (Persons), содержащая информацию об их изобретениях (Artifacts) и содержащая изображения как личностей, так и артефактов (Images) (рис 10.1).

Первичным ключом во всех этих таблицах является поле id (идентификатор). В таблице Artifacts есть поле author, в котором записан идентификатор, присвоенный автору изобретения в таблице Persons. Каждое значение этого поля является внешним ключом для первичного ключа таблицы Persons. Кроме того, в таблицах Persons и Artifacts есть поле photo, которое ссылается на изображение в таблице Images. Эти поля также являются внешними ключами для первичного ключа таблицы Images и устанавливают однозначную логическую связь Persons-Images и Artifacts-Images. То есть если значение внешнего ключа photo в таблице личности равно 10, то это значит, что фотография этой личности имеет id=10 в таблице изображений. Таким образом, внешние ключи используются для организации связей между таблицами базы данных (родительскими и дочерними) и для поддержания ограничений ссылочной целостности данных.

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

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

    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, но я предпочитаю вносить такие изменения на этапе проектирования, поэтому можно внести изменения прямо запрос создания таблицы:

    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

   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;

Загрузка...