15.1.21.5 FOREIGN KEY Constraints — MySQL 9.2

15.1.21.5 Ограничения FOREIGN KEY

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, имя ограничения генерируется автоматически.

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

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

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

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

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

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

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

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

END_OF_DOCUMENT_MARKER

Действия при нарушении целостности ссылки

Когда операция 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 действие по умолчанию всегда равно NO ACTION.

По умолчанию, явно указанное предложение ON DELETE NO ACTION или ON UPDATE NO ACTION не отображается в выводе SHOW CREATE TABLE или в экспортированных таблицах с помощью mysqldump. RESTRICT, которое является эквивалентным не-стандартным ключевым словом, отображается в выводе SHOW CREATE TABLE и в экспортированных таблицах с помощью mysqldump.

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

Для таблиц 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 при нарушении целостности ссылки.

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

В этом простом примере таблицы 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;

MySQL 9.2 поддерживает встроенные REFERENCE предложения, а также неявные первичные ключи родительских таблиц, поэтому второе утверждение CREATE TABLE можно переписать следующим образом:

CREATE TABLE child (
    id INT,
    parent_id INT NOT NULL REFERENCES parent ON DELETE CASCADE,
    INDEX par_ind (parent_id)
) 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 DEFAULT NULL,
  `parent_id` int NOT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

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

END_OF_DOCUMENT_MARKER

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

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

Переменная foreign_key_checks является динамической и поддерживает глобальный и сессионный области. Дополнительную информацию об использовании системных переменных см. в Разделе 7.1.9 «Использование системных переменных».

Отключение проверки внешних ключей полезно, когда:

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

Блокировка

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

Если таблица явно заблокирована с помощью LOCK TABLES, все таблицы, связанные с ограничением внешнего ключа, открываются и блокируются неявно. Для проверок внешних ключей используется общая блокировка только для чтения (LOCK TABLES READ) для связанных таблиц. Для каскадных обновлений используется общая блокировка записи (LOCK TABLES WRITE) для связанных таблиц, участвующих в операции.

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

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

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

Вы можете получить информацию о внешних ключах из таблицы Information Schema 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_FOREIGN и INNODB_FOREIGN_COLS. Примеры запросов показаны ниже:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_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_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
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, 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 8, OS thread handle 140289365317376, query id 14 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 `test`.`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 `test`.`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 000000001e19; asc       ;;
 2: len 7; hex 81000001110137; asc       7;;
...

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

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

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