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

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

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

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

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)
) ENGINE=INNODB;

Вставьте строку в родительскую таблицу, как показано ниже:

mysql> INSERT INTO parent (id) VALUES (1);

Проверьте, что данные были вставлены. Вы можете сделать это, просто выбрав все строки из таблицы parent, как показано здесь:

mysql> SELECT * FROM parent;
+----+
| id |
+----+
|  1 |
+----+

Вставьте строку в дочернюю таблицу с помощью следующего SQL-запроса:

mysql> INSERT INTO child (id,parent_id) VALUES (1,1);

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

Вставка строки в дочернюю таблицу со значением parent_id, которое отсутствует в родительской таблице, отклоняется с ошибкой, как показано здесь:

mysql> INSERT INTO child (id,parent_id) VALUES(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`))

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

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

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`))

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

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

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

DROP TABLE child;

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

Вставьте несколько строк в дочернюю таблицу, используя показанный здесь запрос:

mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);

Проверьте, что данные были вставлены, как показано здесь:

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+

Обновите ID в родительской таблице, изменив его с 1 на 2, используя показанный здесь SQL-запрос:

mysql> UPDATE parent SET id = 2 WHERE id = 1;

Проверьте, что обновление выполнено успешно, выбрав все строки из родительской таблицы, как показано здесь:

mysql> SELECT * FROM parent;
+----+
| id |
+----+
|  2 |
+----+

Проверьте, что действие ON UPDATE CASCADE по ссылочной целостности обновило дочернюю таблицу, как показано здесь:

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+

Чтобы продемонстрировать действие ON DELETE CASCADE по ссылочной целостности, удалите записи из родительской таблицы, где выполняется условие parent_id = 2; это удаляет все записи из родительской таблицы.

mysql> DELETE FROM parent WHERE id = 2;

Поскольку все записи в дочерней таблице связаны с parent_id = 2, действие ON DELETE CASCADE по ссылочной целостности удаляет все записи из дочерней таблицы, как показано здесь:

mysql> SELECT * FROM child;
Empty set (0.00 sec)

Для получения дополнительной информации об ограничениях внешних ключей, см. Раздел 13.1.18.5, «Ограничения внешних ключей».