Ссылочная целостность при помощи FOREIGN KEY в MySQL

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

FOREIGN KEY—особый вид ограничения(constraint) MySQL, которое позволяет предотвратить нарушение ссылочной целостности при удалении/изменении информации в таблицах предках. Поддержка FOREIGN KEY поддерживается только для таблиц типа InnoDB

Пример нарушения ссылочной целостности

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

mysql> SELECT * FROM catalogs;                                                                           
+------------+-------------------------------------+                                                      
| id_catalog | name                                |
+------------+-------------------------------------+                                                      
|          1 | Процессоры                          |                                                      
|          2 | Материнские платы                   |
|          3 | Видеоадаптеры                       |
|          4 | Жёсткие диски                       |
|          5 | Оперативная память                  |
+------------+-------------------------------------+

mysql> SELECT * FROM products;                                                                           
+------------+-------------------------------+------------+
| id_product | name                          | id_catalog |
+------------+-------------------------------+------------+
|          1 | Celeron 1.8                   |          1 |
|          2 | Celeron 2.0GHz                |          1 |
|          3 | Celeron 2.4GHz                |          1 |
|          4 | Celeron D 320 2.4GHz          |          1 |
|          5 | Celeron D 325 2.53GHz         |          1 |
|          6 | Celeron D 315 2.26GHz         |          1 |
|          7 | Intel Pentium 4 3.2GHz        |          1 |
|          8 | Intel Pentium 4 3.0GHz        |          1 |
|          9 | Intel Pentium 4 3.0GHz        |          1 |
|         10 | Gigabyte GA-8I848P-RS         |          2 |
|         11 | Gigabyte GA-8IG1000           |          2 |
|         12 | Gigabyte GA-8IPE1000G         |          2 |
|         13 | Asustek P4C800-E Delux        |          2 |
|         14 | Asustek P4P800-VM\L i865G     |          2 |
|         15 | Epox EP-4PDA3I                |          2 |
|         16 | ASUSTEK A9600XT/TD            |          3 |
|         17 | ASUSTEK V9520X                |          3 |
|         18 | SAPPHIRE 256MB RADEON 9550    |          3 |
|         19 | GIGABYTE AGP GV-N59X128D      |          3 |
|         20 | Maxtor 6Y120P0                |          4 |
|         21 | Maxtor 6B200P0                |          4 |
|         22 | Samsung SP0812C               |          4 |
|         23 | Seagate Barracuda ST3160023A  |          4 |
|         24 | Seagate ST3120026A            |          4 |
|         25 | DDR-400 256MB Kingston        |          5 |
|         26 | DDR-400 256MB Hynix Original  |          5 |
|         27 | DDR-400 256MB PQI             |          5 |
|         28 | DDR-400 512MB Kingston        |          5 |
|         29 | DDR-400 512MB PQI             |          5 |
|         30 | DDR-400 512MB Hynix           |          5 |
+------------+-------------------------------+------------+

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

mysql> DELETE FROM catalogs WHERE name = 'Процессоры';

mysql> SELECT * FROM catalogs;
+------------+-------------------------------------+
| id_catalog | name                                |
+------------+-------------------------------------+
|          2 | Материнские платы                   |
|          3 | Видеоадаптеры                       |
|          4 | Жёсткие диски                       |
|          5 | Оперативная память                  |
+------------+-------------------------------------+

mysql> SELECT * FROM products WHERE id_catalog = 1;
+------------+------------------------+------------+
| id_product | name                   | id_catalog |
+------------+------------------------+------------+
|          1 | Celeron 1.8            |          1 |
|          2 | Celeron 2.0GHz         |          1 |
|          3 | Celeron 2.4GHz         |          1 |
|          4 | Celeron D 320 2.4GHz   |          1 |
|          5 | Celeron D 325 2.53GHz  |          1 |
|          6 | Celeron D 315 2.26GHz  |          1 |
|          7 | Intel Pentium 4 3.2GHz |          1 |
|          8 | Intel Pentium 4 3.0GHz |          1 |
|          9 | Intel Pentium 4 3.0GHz |          1 |
+------------+------------------------+------------+

Это явление называется нарушением ссылочной целостности

На ссылочную целостность базы данных как правило оказывают четыре типа изменений:

  • Добавление новой записи в таблице-потомке. Например добавление новой товарной позиции в таблицу products. Важно заметить что важную роль играет изменение именно таблицы-потомка, т.к изменение таблицы-предка (catalogs) не приведет к нарушению ссылочной целостности, т.к наличие пустой категории товаров допустимо
  • Обновление внешнего ключа в таблице-потомке. Эта ситуация похожа на первую и может произойти при изменении у товара ссылки на несуществующий раздел каталога, например товар с id_catalog равным 50
  • Удаление записи из таблицы-предка. Эта ситуация рассмотрена выше.
  • Изменение записи в таблице-предке. Эта ситуация отличается от рассмотренной выше тем что категория каталога не удаляется а принимает новый id

Обработка изменений при помощи FOREIGN KEY

Для того что бы контролировать ссылочную целостность в базе данных необходимо что бы таблицы были связаны при помощи конструкции FOREIGN KEY, которая имеет вид:


FOREIGN KEY [index_name] (index_col_name, …)
REFERENCES tbl_name (index_col_name,…)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

FOREIGN KEY — используется при создании/изменении таблиц-потомков таблицах. В рамках данной статьи FOREIGN KEY, следует использовать в таблице products. Данная конструкция позволяет задать в таблице-потомке внешний ключ с именем index_name на столбцах таблицы которые перечисляется в круглых скобках. Можно использовать один или несколько столбцов.

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

Необязательные конструкции ON DELETE и ON UPDATE, определяют поведение MySQL при удалении/обновлении записей из таблицы-предка.

Допустимые параметры для ключевых слов ON DELETE и ON UPDATE:

  • RESTRICT — Если в таблице-потомке существуют записи ссылающиеся на первичный ключ таблицы-предка то при удалении или обновлении записей с этим первичным ключом в таблице предке, будет возвращена ошибка. Ошибка будет возвращаться до тех пор пока не останется ни одной ссылки в таблице потомке. В MySQL данный параметр означает то же самое что и NO ACTION
  • CASCADE — При удалении/обновлении записей в таблице-предке, будут так же обновлены/удалены записи из таблицы-потомка с существующим первичным ключом
  • SET NULL — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом будут обновлены на NULL
  • NO ACTION — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом изменены не будут. В MySQL данный параметр означает то же самое что и RESTRICT
  • SET DEFAULT — Это действие зарезервировано но не обрабатывается в InnoDB

Добавление для таблицы products из примера статьи конструкции:

ALTER TABLE products ADD CONSTRAINT fk_catalog
FOREIGN KEY (id_catalog) REFERENCES catalogs (id_catalog)
    ON DELETE CASCADE
    ON UPDATE CASCADE

приведет к тому что изменения таблицы catalogs приведет к автоматическому изменению таблицы products.

Проверку ограничения внешнего ключа можно отключить присвоив системной переменной FOREIGN_KEY_CHECKS значение 0

mysql> FOREIGN_KEY_CHECKS = 0;

Примеры контроллирования ссылочной целостности при помощи FOREIGN_KEY

Каскадное обновление

Пример изменения первичного ключа в таблице предке с правилом удаления о обновления CASCADE, когда на данную запись существует ссылки (внешние ключи) в таблице потомке.

mysql>UPDATE catalogs SET id_catalog = 5000 WHERE id_catalog = 5;

mysql> SELECT * FROM catalogs;
+------------+-------------------------------------+
| id_catalog | name                                |
+------------+-------------------------------------+
|          2 | Материнские платы                   |
|          3 | Видеоадаптеры                       |
|          4 | Жёсткие диски                       |
|       5000 | Оперативная память                  |
+------------+-------------------------------------+

mysql> SELECT * FROM products where id_catalog = 5;                                                                           
Empty set (0,01 sec)

mysql> SELECT * FROM products where id_catalog = 5000;
+------------+-------------------------------+------------+
| id_product | name                          | id_catalog |
+------------+-------------------------------+------------+
|         25 | DDR-400 256MB Kingston        |       5000 |
|         26 | DDR-400 256MB Hynix Original  |       5000 |
|         27 | DDR-400 256MB PQI             |       5000 |
|         28 | DDR-400 512MB Kingston        |       5000 |
|         29 | DDR-400 512MB PQI             |       5000 |
|         30 | DDR-400 512MB Hynix           |       5000 |
+------------+-------------------------------+------------+

Важно отметить что записи с внешними ключами сами могут выступать в качестве ссылок для внешних ключей других таблиц. Таким образом выполнение одного запроса может привести к значительным измнения в БД.

Comments

  • Спасибо, очень познавательная статья. Все рассказано максимально просто и доступно.
    Продолжайте в этом духе

    Oleg Zadorozhnyi17.12.2012
  • Отличная статья …

    Сергей11.02.2014
  • Классная статья! Желаю успехов!

    Sarvar23.02.2014
  • молодец

    V24.02.2014
  • Что за бред?! С каких это пор «NO ACTION — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом изменены не будут»?
    Когда всю жизнь «NO ACTION same as RESTRICT».

    Guru07.03.2014
    • NO ACTION в MySQL работает так же как и RESTRICT. RESTRICT — не позволяет изменять/удалять, соответственно NO ACTION тоже.

      root29.03.2014
  • >NO ACTION — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом изменены не будут. В MySQL данный параметр означает то же самое что и RESTRICT

    Просто написано недостаточно ясно/просто. Можно подумать, что в таблице-«предке» запись будет удалена/изменена а в таблице-«потомке» нет.

    Дмитрий04.06.2014
    • Предложите свою формулировку

      root04.06.2014
  • CASCADE — При удалении/обновлении записей в таблице-предке, будут так же УДАЛЕНЫ записи из таблицы-потомка с существующим первичным ключом

    —>

    CASCADE — При удалении/обновлении записей в таблице-предке, будут так же ОБНОВЛЕНЫ/УДАЛЕНЫ записи из таблицы-потомка с существующим первичным ключом

    Александр20.09.2014
  • Хорошая статья, спасибо. Это некий аналог триггеров, только в упрощенной форме. Хотелось бы увидеть более подробное рассмотрение ситуации с обновлением таблиц — как именно происходит обновление таблицы-потомка, а также какие поля и на какие значения меняются.

    Shadow07.09.2016
    • Добавил пример с обновлением

      root18.09.2016
  • У вас в примере каскадного обновления закралась ошибка в запросе:

    > mysql>UPDATE catalogs SET id_catalog = 6 WHERE id_catalog = 5000;

    Далее из контекста получается, что должно быть:

    UPDATE catalogs SET id_catalog = 5000 WHERE id_catalog = 5;

    white06.10.2016
    • Совершенно верно. Исправил, спасибо.

      root20.11.2016
  • помогло. писибо. только вопрос а как узнать параметр этого FOREIGN_KEY_CHECKS
    не установить а именно узнать?
    в SHOW VARIABLES его нет.

    serg11.12.2016
    • Пожалуйста. Обращайтесь.
      Узнать состояние FOREIGN_KEY_CHECKS можно двумя способами. Все запросы советую выполнять в терминале.

      1. Из списка глобальных переменных. (то, о чем вы спрашивали)

      mysql> SHOW VARIABLES WHERE Variable_name LIKE '%foreign%';
      +--------------------+-------+
      | Variable_name      | Value |
      +--------------------+-------+
      | foreign_key_checks | ON    |
      +--------------------+-------+
      1 row in set (0,00 sec)
      

      2. При помощи отображения значения конкретной переменной

      mysql> SELECT @@FOREIGN_KEY_CHECKS;
      +----------------------+
      | @@FOREIGN_KEY_CHECKS |
      +----------------------+
      |                    1 |
      +----------------------+
      1 row in set (0,00 sec)
      
      root04.01.2017
  • Благодарю за материал, действительно, помогли.

    sql.noob03.03.2017
  • Спасибо за материал, помогли!! Мучался

    Дмитрий Гамзин07.03.2017
  • Спасибо, за статью, даже мне — новичку, всё понятно.

    Romannn16.04.2017

Добавить комментарий