Если в родительской таблице есть несколько строк с одинаковым значением ключевого поля ссылки, InnoDB выполняет проверку внешнего ключа так, как если бы других строк родительской таблицы с одинаковым значением ключа не существовало. Например, если вы определяете ограничение типа RESTRICT, и есть строка дочерней таблицы со множеством строк родительской таблицы, InnoDB не допускает удаление ни одной из строк родительской таблицы. Это показано в следующем примере:

mysql> CREATE TABLE parent (
    ->     id INT,
    ->     INDEX (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE child (
    ->     id INT,
    ->     parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     FOREIGN KEY (parent_id)
    ->         REFERENCES parent(id)
    ->         ON DELETE RESTRICT
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO parent (id)
    ->     VALUES ROW(1), ROW(2), ROW(3), ROW(1);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO child (id,parent_id)
    ->     VALUES ROW(1,1), ROW(2,2), ROW(3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DELETE FROM parent WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
(`parent_id`) REFERENCES `parent` (`id`) ON DELETE RESTRICT)

Если ON UPDATE CASCADE или ON UPDATE SET NULL рекурсивно обновляют одну и ту же таблицу, которую они ранее обновили в рамках одной каскадной операции, то они ведут себя как RESTRICT. Это означает, что вы не можете использовать самоссылочные ON UPDATE CASCADE или ON UPDATE SET NULL операции. Это предотвращает бесконечные циклы, возникающие из-за каскадных обновлений. Самоссылочные ON DELETE SET NULL, в свою очередь, возможны, как и самоссылочные ON DELETE CASCADE. Каскадные операции не могут быть вложены глубже 15 уровней.

В операторе SQL, который вставляет, удаляет или обновляет множество строк, ограничения внешних ключей (как и ограничения уникальности) проверяются по строкам. При выполнении проверок внешних ключей InnoDB устанавливает общие блокировки на уровне строк для дочерних или родительских записей, которые необходимо проверить. MySQL проверяет ограничения внешних ключей немедленно; проверка не откладывается до подтверждения транзакции. Согласно стандарту SQL, поведение по умолчанию должно быть отложенной проверкой. То есть, ограничения проверяются только после обработки всего оператора SQL. Это означает, что невозможно удалить строку, которая ссылается на себя с помощью внешнего ключа.

Ни один из двигателей хранилища, включая InnoDB, не распознает или не выполняет ограничение MATCH, используемое в определениях ограничений целостности ссылок. Использование явного MATCH предложения не имеет указанного эффекта и приводит к тому, что предложения ON DELETE и ON UPDATE игнорируются. Следует избегать указания MATCH.

Предложение MATCH в стандарте SQL управляет тем, как обрабатываются значения NULL в составном (многоколонном) внешнем ключе при сравнении с первичным ключом в таблице ссылок. MySQL по существу реализует семантику, определённую MATCH SIMPLE, что позволяет внешнему ключу быть полностью или частично NULL. В этом случае строка (дочерней таблицы), содержащая такой внешний ключ, может быть вставлена, даже если она не соответствует никакой строке в таблице ссылок (родительской). (Можно реализовать другую семантику, используя триггеры.)

Ограничение FOREIGN KEY, ссылающееся на не-UNIQUE ключ, не является стандартным SQL, а является расширением InnoDB, которое сейчас устарело и должно быть включено, установив restrict_fk_on_non_standard_key. Следует ожидать, что поддержка использования нестандартных ключей будет удалена в будущих версиях MySQL, и перейти к их использованию сейчас.

Двигатель хранилища NDB требует явного уникального ключа (или первичного ключа) на любом поле, указанном как внешний ключ, в соответствии со стандартом SQL.

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

MySQL анализирует, но игнорирует “встроенные REFERENCES спецификации” (как определено в стандарте SQL), где ссылки определены как часть спецификации столбца. MySQL принимает REFERENCES предложения только в случае указания в отдельной FOREIGN KEY спецификации.

Определение столбца с использованием REFERENCES tbl_name(col_name) предложения не имеет реального эффекта и служит только как напоминание или комментарий о том, что столбец, который вы в данный момент определяете, предназначен для ссылки на столбец в другой таблице. Важно понимать при использовании этого синтаксиса, что:

Созданный таким образом столбец можно использовать как столбец для объединения, как показано здесь:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
    ROW(NULL, 'polo', 'blue', @last),
    ROW(NULL, 'dress', 'white', @last),
    ROW(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
    ROW(NULL, 'dress', 'orange', @last),
    ROW(NULL, 'polo', 'red', @last),
    ROW(NULL, 'dress', 'blue', @last),
    ROW(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+

SELECT s.* FROM person p INNER JOIN shirt s
   ON s.owner = p.id
WHERE p.name LIKE 'Lilliana%'
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

Когда используется в таком формате, предложение REFERENCES не отображается в выводе SHOW CREATE TABLE или DESCRIBE:

mysql> SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci