15.1.20.5 FOREIGN KEY Constraints — MySQL 8.4

15.1.20.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, если оно определено.
Если опущена фраза CONSTRAINT symbol или после ключевого слова CONSTRAINT нет символа, имя ограничения генерируется автоматически.

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

Идентификаторы таблиц и столбцов в фразе 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 в качестве действий при нарушении ссылочной целостности, а также SET NULL или SET DEFAULT в качестве действий при нарушении ссылочной целостности.

Ограничение внешнего ключа на базовом столбце хранимого генерируемого столбца не может использовать 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;

Это более сложный пример, в котором таблица 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 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=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, которая относится к коду ошибки: 150 в сообщении об ошибке, что означает, что ограничение внешнего ключа было некорректно сформировано.
Изменение таблицы возвращает ошибку (код ошибки: 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 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Вы можете получить информацию о внешних ключах из таблицы Информационной схемы 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), информацию об последней ошибке внешнего ключа можно получить, проверив вывод 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, а не вызывающий пользователь. Если у этого пользователя есть права на уровне родительской таблицы, информация о родительской таблице по-прежнему отображается. В этом случае ответственность за сокрытие информации лежит на создателе хранимой программы, в том числе с помощью обработки соответствующих условий.