13.1.18.5 FOREIGN KEY Constraints — MySQL 5.7

13.1.18.5 Ограничения внешнего ключа

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

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

Основный синтаксис для определения ограничения внешнего ключа в операторе CREATE TABLE или ALTER TABLE включает следующее:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Использование ограничений внешних ключей описано в следующих разделах этого раздела:

Идентификаторы

Имена ограничений внешних ключей регулируются следующими правилами:

Используется значение CONSTRAINT symbol, если оно определено.
Если опущена фраза CONSTRAINT symbol или символ не указан после ключевого слова CONSTRAINT:
- Для таблиц InnoDB имя ограничения генерируется автоматически.
- Для таблиц NDB используется значение FOREIGN KEY index_name, если оно определено. В противном случае имя ограничения генерируется автоматически.
Значение CONSTRAINT symbol, если оно определено, должно быть уникальным в базе данных. Повторение symbol приводит к ошибке, похожей на: Ошибка 1005 (HY000): Не удалось создать таблицу 'test.fk1' (ошибка: 121).

Идентификаторы таблиц и столбцов в фразе FOREIGN KEY ... REFERENCES могут быть заключены в обратные кавычки (`). В качестве альтернативы можно использовать двойные кавычки ("), если включен режим SQL ANSI_QUOTES. Также учитывается значение системной переменной lower_case_table_names.

Условие и ограничения

Ограничения внешних ключей подчиняются следующим условиям и ограничениям:

Родительская и дочерняя таблицы должны использовать один и тот же движок хранения и не могут быть временными таблицами.
Для создания ограничения внешнего ключа требуется право REFERENCES на родительскую таблицу.
Соответствующие столбцы во внешнем ключе и ключе ссылки должны иметь одинаковые типы данных. Размер и знак типов с фиксированной точностью, таких как INTEGER и DECIMAL, должны быть одинаковыми. Длина строковых типов не обязана быть одинаковой. Для столбцов небинарных (символьных) строк набор символов и сортировка должны быть одинаковыми.
MySQL поддерживает ссылки внешних ключей между одним столбцом и другим внутри таблицы. (Столбец не может иметь ссылку внешнего ключа на себя). В этих случаях «запись дочерней таблицы» относится к зависимой записи в той же таблице.
MySQL требует индексов на внешних ключах и ключах ссылок, чтобы проверки внешних ключей были быстрыми и не требовали сканирования всей таблицы. В таблице ссылок должен существовать индекс, где столбцы внешнего ключа перечислены как первые столбцы в том же порядке. Такой индекс создается в таблице ссылок автоматически, если он не существует. Этот индекс может быть неявным образом удален позже, если вы создадите другой индекс, который можно использовать для принудительного выполнения ограничения внешнего ключа. index_name, если указано, используется, как описано ранее.
InnoDB позволяет внешнему ключу ссылаться на любой столбец индекса или группу столбцов. Однако в таблице ссылки должен существовать индекс, в котором столбцы ссылок являются первыми столбцами в том же порядке. Скрытые столбцы, которые InnoDB добавляет в индекс, также учитываются (см. Раздел 14.6.2.1, «Кластеризованные и вторичные индексы»).

NDB требует явного уникального ключа (или первичного ключа) для любого столбца, на который ссылается внешний ключ. InnoDB этого не требует, что является расширением стандартного SQL.
Префиксы индексов для столбцов внешнего ключа не поддерживаются. Следовательно, столбцы BLOB и TEXT не могут быть включены во внешний ключ, потому что индексы на этих столбцах должны всегда включать длину префикса.
InnoDB в настоящее время не поддерживает внешние ключи для таблиц с пользовательским разбиением.

Это ограничение не относится к таблицам NDB, которые разделены по KEY или LINEAR KEY (единственные типы пользовательского разбиения, поддерживаемые движком хранения NDB); они могут иметь ссылки внешних ключей или быть объектами таких ссылок.
Таблица в отношении внешнего ключа не может быть изменена для использования другого движка хранения. Для изменения движка хранения необходимо сначала удалить все ограничения внешних ключей.
Ограничение внешнего ключа не может ссылаться на виртуальный сгенерированный столбец.
До версии 5.7.16 ограничение внешнего ключа не может ссылаться на вторичный индекс, определенный для виртуального сгенерированного столбца.

Сведения о том, чем реализация ограничений внешних ключей в MySQL отличается от стандарта SQL, см. в Разделе 1.6.2.3, «Отличия в ограничении FOREIGN KEY».

Действия при ссылке

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

CASCADE: Удаление или обновление строки из родительской таблицы и автоматическое удаление или обновление соответствующих строк в дочерней таблице. Поддерживаются как ON DELETE CASCADE, так и ON UPDATE CASCADE. Не определять несколько ON UPDATE CASCADE, которые действуют на один и тот же столбец в родительской или дочерней таблице.

Если в обоих таблицах в отношении внешнего ключа определён подзапрос FOREIGN KEY, что делает обе таблицы родительскими и дочерними, то подзапрос ON UPDATE CASCADE или ON DELETE CASCADE, определенный для одного FOREIGN KEY, должен быть определен для другого, чтобы каскадные операции выполнились успешно. Если подзапрос ON UPDATE CASCADE или ON DELETE CASCADE определен только для одного FOREIGN KEY, каскадные операции завершаются ошибкой.

Примечание

Каскадные действия внешнего ключа не активируют триггеры.
SET NULL: Удаление или обновление строки из родительской таблицы и установка столбца или столбцов внешнего ключа в дочерней таблице в значение NULL. Поддерживаются как ON DELETE SET NULL, так и ON UPDATE SET NULL.

Если вы указываете действие SET NULL, убедитесь, что вы не объявили столбцы в дочерней таблице как NOT NULL.
RESTRICT: Отклоняет операцию удаления или обновления для родительской таблицы. Указание RESTRICT (или NO ACTION) эквивалентно опущению подзапросов ON DELETE или ON UPDATE.
NO ACTION: Ключевое слово из стандартного SQL. Для InnoDB это эквивалентно RESTRICT; операция удаления или обновления для родительской таблицы немедленно отклоняется, если в связанной таблице есть соответствующее значение внешнего ключа. NDB поддерживает отложенные проверки, и NO ACTION задаёт отложенную проверку; при использовании этого подхода, проверки ограничений не выполняются до момента фиксации. Обратите внимание, что для таблиц NDB это приводит к отложению всех проверок внешнего ключа для родительских и дочерних таблиц.
SET DEFAULT: Это действие распознаётся парсером MySQL, но как InnoDB, так и NDB отклоняют определения таблиц, содержащие подзапросы ON DELETE SET DEFAULT или ON UPDATE SET DEFAULT.

Для двигателей хранения, которые поддерживают внешние ключи, MySQL отклоняет любую операцию INSERT или UPDATE, которая пытается создать значение внешнего ключа в дочерней таблице, если нет соответствующего кандидата в родительской таблице.

Для ON DELETE или ON UPDATE, если они не указаны, по умолчанию всегда используется RESTRICT.

Для таблиц NDB, ON UPDATE CASCADE не поддерживается, если ссылка относится к первичному ключу родительской таблицы.

Начиная с NDB 7.5.14 и NDB 7.6.10: для таблиц NDB, ON DELETE CASCADE не поддерживается, если дочерняя таблица содержит один или несколько столбцов типа TEXT или BLOB. (Ошибка #89511, Ошибка #27484882)

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

Ограничение внешнего ключа на хранимом сгенерированном столбце не может использовать CASCADE, SET NULL или SET DEFAULT в качестве ON UPDATE действий при ссылке, а также не может использовать SET NULL или SET DEFAULT в качестве ON DELETE действий при ссылке.

Ограничение внешнего ключа на базовом столбце хранимого сгенерированного столбца не может использовать CASCADE, SET NULL или SET DEFAULT в качестве ON UPDATE или ON DELETE действий при ссылке.

В MySQL 5.7.13 и более ранних версиях, InnoDB не позволяет определять ограничение внешнего ключа с каскадным действием при ссылке на индексированный виртуальный сгенерированный столбец. Это ограничение снято в MySQL 5.7.14.

В MySQL 5.7.13 и более ранних версиях, InnoDB не позволяет определять каскадные действия при ссылке на невиртуальные столбцы внешних ключей, которые явно включены в . Это ограничение снято в MySQL 5.7.14.

Примеры ограничений внешнего ключа

В этом простом примере таблицы parent и child связаны с помощью внешнего ключа с одним столбцом:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

Это более сложный пример, в котором таблица product_order имеет внешние ключи для двух других таблиц. Один внешний ключ ссылается на индекс из двух столбцов в таблице product. Другой ссылается на индекс из одного столбца в таблице customer:

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

Добавление ограничений внешнего ключа

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

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

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

Удаление ограничений внешнего ключа

Вы можете удалить ограничение внешнего ключа, используя следующий синтаксис ALTER TABLE:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Если в подзапросе FOREIGN KEY было определено имя CONSTRAINT при создании ограничения, вы можете использовать это имя для удаления ограничения внешнего ключа. В противном случае имя ограничения генерируется внутренне, и вам необходимо использовать это значение. Чтобы определить имя ограничения внешнего ключа, используйте SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;

Добавление и удаление внешнего ключа в одном операторе ALTER TABLE поддерживается для ALTER TABLE ... ALGORITHM=INPLACE. Это не поддерживается для ALTER TABLE ... ALGORITHM=COPY.

Проверки внешних ключей

В MySQL таблицы InnoDB и NDB поддерживают проверку ограничений внешних ключей. Проверка внешних ключей управляется переменной foreign_key_checks, которая включена по умолчанию. Обычно эту переменную оставляют включённой во время нормальной работы для обеспечения целостности ссылок. Переменная foreign_key_checks оказывает такое же влияние на таблицы NDB, как и на таблицы InnoDB.

Переменная foreign_key_checks является динамической и поддерживает глобальный и сеансовый области действия. Сведения о работе с системными переменными см. в разделе 5.1.8, «Использование системных переменных».

Отключение проверки внешних ключей полезно в следующих случаях:

Удаление таблицы, на которую ссылается ограничение внешнего ключа. Ссылающаяся таблица может быть удалена только после отключения foreign_key_checks. При удалении таблицы также удаляются ограничения, определённые на этой таблице.
Загрузка таблиц в другом порядке, чем требуется их отношениями внешних ключей. Например, mysqldump генерирует правильные определения таблиц в файле дампов, включая ограничения внешних ключей для дочерних таблиц. Для упрощения загрузки файлов дампов таблиц с отношениями внешних ключей, mysqldump автоматически включает в выходные данные дампов оператор, который отключает foreign_key_checks. Это позволяет загрузить таблицы в любом порядке, если в файле дампов содержатся таблицы, которые не упорядочены правильно для внешних ключей. Отключение foreign_key_checks также ускоряет процесс загрузки, так как избегает проверки внешних ключей.
Выполнение операций LOAD DATA для избегания проверки внешних ключей.
Выполнение операции ALTER TABLE над таблицей, имеющей отношение внешнего ключа.

Когда foreign_key_checks отключено, ограничения внешних ключей игнорируются, за исключением следующих случаев:

Повторное создание ранее удалённой таблицы возвращает ошибку, если определение таблицы не соответствует ограничениям внешних ключей, которые ссылаются на таблицу. Таблица должна иметь правильные имена и типы столбцов. Она также должна иметь индексы на ссылаемых ключах. Если эти требования не выполнены, MySQL возвращает ошибку 1005, которая в сообщении об ошибке ссылается на errno: 150, что означает, что ограничение внешнего ключа не было правильно сформировано.
Изменение таблицы возвращает ошибку (errno: 150), если определение внешнего ключа неправильно сформировано для изменённой таблицы.
Удаление индекса, необходимого для ограничения внешнего ключа. Ограничение внешнего ключа должно быть удалено перед удалением индекса.
Создание ограничения внешнего ключа, где столбец ссылается на столбец с несовместимым типом.

Отключение foreign_key_checks имеет следующие дополнительные последствия:

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

Определения и метаданные внешних ключей

Для просмотра определения внешнего ключа используйте SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Информацию об внешних ключах можно получить из таблицы KEY_COLUMN_USAGE Схемы Информации. Пример запроса к этой таблице показан ниже:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+------------+-------------+-----------------+
| test         | child      | parent_id   | child_ibfk_1    |
+--------------+------------+-------------+-----------------+

Информацию, специфичную для InnoDB внешних ключей, можно получить из таблиц INNODB_SYS_FOREIGN и INNODB_SYS_FOREIGN_COLS. Примеры запросов показаны ниже:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
      ID: test/child_ibfk_1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS \G
*************************** 1. row ***************************
          ID: test/child_ibfk_1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0

Ошибки внешних ключей

В случае ошибки внешнего ключа, связанной с InnoDB таблицами (обычно ошибка 150 в MySQL Server), информацию об последней ошибке внешнего ключа можно получить, проверив вывод SHOW ENGINE INNODB STATUS.

mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-10-16 18:35:18 0x7fc2a95c1700 Transaction:
TRANSACTION 1814, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 2, OS thread handle 140474041767680, query id 74 localhost
root update
INSERT INTO child VALUES
    (NULL, 1)
    , (NULL, 2)
    , (NULL, 3)
    , (NULL, 4)
    , (NULL, 5)
    , (NULL, 6)
Foreign key constraint fails for table `mysql`.`child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent`
  (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

But in parent table `mysql`.`parent`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000000070a; asc       ;;
 2: len 7; hex aa0000011d0134; asc       4;;
...

Предупреждение

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