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 ROW(1);

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

mysql> TABLE parent;
+----+
| id |
+----+
|  1 |
+----+

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

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

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

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

mysql> INSERT INTO child (id,parent_id) VALUES ROW(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 VALUES = 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 ROW(1,1), ROW(2,1), ROW(3,1);

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

mysql> TABLE child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+

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

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

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

mysql> TABLE parent;
+----+
| id |
+----+
|  2 |
+----+

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

mysql> TABLE 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> TABLE child;
Empty set (0.00 sec)

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