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 |
+------+-----------+

Обновите идентификатор в родительской таблице, изменив его с 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.20.5, «Ограничения внешних ключей».