Внешний ключ InnoDB: политика удаления должна быть явной

В базе есть клиент и связанные с ним заказы. Что делать с заказами, если клиента удаляют: запретить удаление, убрать заказы вместе с ним или оставить их без ссылки? Это решение стоит принять до настройки внешнего ключа. Посмотрим, как выразить его в MySQL 8.0 с таблицами InnoDB.

Внешний ключ с RESTRICT

CREATE TABLE demo_customers (id integer PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE demo_orders (
    id integer PRIMARY KEY,
    customer_id integer NOT NULL,
    CONSTRAINT fk_demo_customer FOREIGN KEY (customer_id)
      REFERENCES demo_customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
INSERT INTO demo_customers VALUES (1);
INSERT INTO demo_orders VALUES (10, 1);
-- Каждую ошибочную команду выполняйте отдельно.
INSERT INTO demo_orders VALUES (11, 999);
DELETE FROM demo_customers WHERE id = 1;

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

Что означает каскад

ON DELETE CASCADE автоматически удаляет связанные дочерние строки. Это удобно для действительно зависимых сущностей, но опасно применять по привычке к истории операций. SET NULL сохраняет дочернюю строку без ссылки и требует допускающего NULL столбца. Для InnoDB NO ACTION действует как немедленная проверка RESTRICT, поэтому нельзя ожидать отложенного контроля только из-за другого названия.

Типы столбцов и индексы

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

Отключать foreign_key_checks ради обычной вставки не стоит: повторное включение не исправит уже загруженные нарушения. Для импорта спланируйте порядок родителей и дочерних строк, затем найдите потерянные ссылки отдельным запросом. Проверьте корректную вставку, неизвестного клиента и удаление, а в приложении предусмотрите понятное сообщение об отказе.

Источники и дальнейшее чтение

• MySQL 8.0: FOREIGN KEY
• MySQL 8.0: особенности внешних ключей