Раздел 34 · MySQL с нуля

Внешние ключи и ссылочная целостность

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке первичный ключ дал каждой строке свой номер. Номер нужен не только для порядка: по нему на строку ссылаются другие таблицы. Сеанс в screenings хранит film_id, номер фильма, а билет хранит screening_id. Что будет, если номер указывает на фильм, которого нет? Сегодня разбираем внешние ключи: правило, по которому MySQL не даёт ссылкам повиснуть в пустоте.

Формуляр в библиотеке

Возьмёшь в библиотеке книгу, и в твой формуляр запишут: «книга № 4512». Библиотекарь следит за двумя вещами. Он не запишет номер книги, которой нет в каталоге. И он не спишет книгу из каталога, пока она числится в чьём-то формуляре. Без такого присмотра через год в формулярах окажутся номера списанных книг, и никто не скажет, у кого они на руках.

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

Внешние ключи в kino

В базе kino внешние ключи уже стоят. Посмотрим на сеансы:

SHOW CREATE TABLE screenings\G
*************************** 1. row ***************************
       Table: screenings
Create Table: CREATE TABLE `screenings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `film_id` int NOT NULL,
  `hall_id` int NOT NULL,
  `starts_at` datetime NOT NULL,
  `price` decimal(7,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `film_id` (`film_id`),
  KEY `hall_id` (`hall_id`),
  CONSTRAINT `screenings_ibfk_1` FOREIGN KEY (`film_id`) REFERENCES `films` (`id`),
  CONSTRAINT `screenings_ibfk_2` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Строка FOREIGN KEY (film_id) REFERENCES films (id) читается так: значение film_id должно найтись в колонке id таблицы films. В kino ключи объявлены без имён, и MySQL назвал их сам: имя таблицы, _ibfk_ и порядковый номер.

Обрати внимание на строки KEY film_id и KEY hall_id. Мы их не просили. Это индексы, и MySQL создал их сам, потому что внешнему ключу нужен индекс на дочерней колонке. Без него каждое удаление фильма заставляло бы базу перебирать все сеансы в поисках ссылок. Что такое индекс, разберём в уроке про индексы.

Две ошибки, которые охраняют данные

Сеанс фильма номер 99, которого нет:

INSERT INTO screenings (film_id, hall_id, starts_at, price) VALUES (99, 1, '2026-10-27 19:00:00', 400);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`kino`.`screenings`, CONSTRAINT `screenings_ibfk_1` FOREIGN KEY (`film_id`) REFERENCES `films` (`id`))

Ошибка 1452 говорит: «не могу добавить дочернюю строку». В скобках видно, какой ключ сработал. Это библиотекарь, который не пишет в формуляр несуществующую книгу.

Теперь с другой стороны. Удалим «Брата», у которого два сеанса:

DELETE FROM films WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`kino`.`screenings`, CONSTRAINT `screenings_ibfk_1` FOREIGN KEY (`film_id`) REFERENCES `films` (`id`))

Ошибка 1451: «не могу удалить родительскую строку», на неё ссылаются. То же будет, если поменять номер родителя. В VIP-зале идёт сеанс, и перенумеровать зал не выйдет:

UPDATE halls SET id = 10 WHERE id = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`kino`.`screenings`, CONSTRAINT `screenings_ibfk_2` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`id`))

А «Солярис» удаляется спокойно: сеансов у него нет, ссылок нет.

DELETE FROM films WHERE id = 7;
Query OK, 1 row affected (0.00 sec)

Что делать при удалении родителя

Запрет удаления подходит не всегда. Заведём таблицу оценок фильмов и для каждой ссылки выберем своё поведение. Его задают словами ON DELETE:

CREATE TABLE film_reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  film_id INT NOT NULL,
  viewer_id INT,
  stars TINYINT NOT NULL,
  FOREIGN KEY (film_id) REFERENCES films (id) ON DELETE CASCADE,
  FOREIGN KEY (viewer_id) REFERENCES viewers (id) ON DELETE SET NULL
);
INSERT INTO film_reviews (film_id, viewer_id, stars) VALUES (6, 1, 4), (6, 6, 5), (5, 6, 5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Логика такая. Удалили фильм, и оценки ему больше не нужны: ON DELETE CASCADE удалит их вместе с фильмом, каскадом. Удалился зритель, а оценку жалко терять, она участвует в рейтинге фильма: ON DELETE SET NULL оставит оценку и сотрёт ссылку на автора.

Удалим Егора Ёлкина, у него две оценки и ни одного билета:

DELETE FROM viewers WHERE id = 6;
SELECT * FROM film_reviews;
+----+---------+-----------+-------+
| id | film_id | viewer_id | stars |
+----+---------+-----------+-------+
|  1 |       6 |         1 |     4 |
|  2 |       6 |      NULL |     5 |
|  3 |       5 |      NULL |     5 |
+----+---------+-----------+-------+
3 rows in set (0.00 sec)

Оценки остались, автор стал NULL. Теперь удалим фильм «Новые имена». У него есть сеанс, а ключ сеансов каскада не разрешает. Сначала убираем сеанс, на него билетов не продали, потом фильм:

DELETE FROM screenings WHERE film_id = 6;
DELETE FROM films WHERE id = 6;
SELECT * FROM film_reviews;
+----+---------+-----------+-------+
| id | film_id | viewer_id | stars |
+----+---------+-----------+-------+
|  3 |       5 |      NULL |     5 |
+----+---------+-----------+-------+
1 row in set (0.00 sec)

Две оценки фильма 6 исчезли вместе с ним. Если не написать ON DELETE, действует запрет, как у сеансов: MySQL называет его NO ACTION или RESTRICT, в InnoDB это одно и то же.

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

Имя для ключа

Имя screenings_ibfk_1 мало что говорит, особенно в ошибке, которая прилетела в журнал приложения среди ночи. Ключу можно дать имя самому, словом CONSTRAINT перед FOREIGN KEY:

CREATE TABLE hall_notes (id INT PRIMARY KEY, hall_id INT NOT NULL, CONSTRAINT fk_hall_notes_hall FOREIGN KEY (hall_id) REFERENCES halls (id));
INSERT INTO hall_notes VALUES (1, 9);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`kino`.`hall_notes`, CONSTRAINT `fk_hall_notes_hall` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`id`))

Зала 9 нет, и по имени fk_hall_notes_hall сразу видно, какая связь не сошлась: заметки и залы. Частая схема имени: fk_, дочерняя таблица, родительская. Имя пригодится и потом, когда ключ понадобится удалить через ALTER TABLE.

Из внешних ключей следует и порядок работы с таблицами. Создают сначала родителя, потом ребёнка: сослаться на таблицу, которой ещё нет, нельзя. Удаляют наоборот, сначала ребёнка. Иначе MySQL откажет, как увидишь ниже на примере DROP TABLE films.

Ошибки при объявлении ключа

Внешний ключ строже, чем кажется. Типы колонок должны совпадать:

CREATE TABLE bad_fk (id INT PRIMARY KEY, film_id BIGINT, FOREIGN KEY (film_id) REFERENCES films (id));
ERROR 3780 (HY000): Referencing column 'film_id' and referenced column 'id' in foreign key constraint 'bad_fk_ibfk_1' are incompatible.

films.id это INT, а ссылка BIGINT. Для людей числа одинаковые, для MySQL нет.

Ссылаться можно только на первичный ключ или колонку с UNIQUE. Название фильма не уникально, и ссылка на него не пройдёт:

CREATE TABLE bad_fk (id INT PRIMARY KEY, film_title VARCHAR(100), FOREIGN KEY (film_title) REFERENCES films (title));
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'bad_fk_ibfk_1' in the referenced table 'films'

Иначе непонятно, на какой из двух одноимённых фильмов указывает ссылка.

Ещё три ошибки, которые встретятся рано или поздно. Опечатка в имени таблицы, SET NULL на колонке с NOT NULL и попытка удалить таблицу, на которую ссылаются:

CREATE TABLE bad_fk (id INT PRIMARY KEY, film_id INT, FOREIGN KEY (film_id) REFERENCES movies (id));
ERROR 1824 (HY000): Failed to open the referenced table 'movies'
CREATE TABLE bad_fk (id INT PRIMARY KEY, film_id INT NOT NULL, FOREIGN KEY (film_id) REFERENCES films (id) ON DELETE SET NULL);
ERROR 1830 (HY000): Column 'film_id' cannot be NOT NULL: needed in a foreign key constraint 'bad_fk_ibfk_1' SET NULL
DROP TABLE films;
ERROR 3730 (HY000): Cannot drop table 'films' referenced by a foreign key constraint 'screenings_ibfk_1' on table 'screenings'.

Ссылка, которую MySQL не заметил

Есть ловушка для тех, кто пришёл из PostgreSQL. Там REFERENCES можно написать прямо у колонки, и ключ появится, как в уроке PostgreSQL про внешние ключи. MySQL такую запись разбирает и молча выбрасывает:

CREATE TABLE inline_fk (id INT PRIMARY KEY, film_id INT REFERENCES films (id));
INSERT INTO inline_fk VALUES (1, 99);
Query OK, 1 row affected (0.00 sec)

Фильма 99 нет, а строка вставилась. SHOW CREATE TABLE подтверждает: ключа нет.

SHOW CREATE TABLE inline_fk\G
*************************** 1. row ***************************
       Table: inline_fk
Create Table: CREATE TABLE `inline_fk` (
  `id` int NOT NULL,
  `film_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

В MySQL внешний ключ пиши отдельной строкой: FOREIGN KEY (колонка) REFERENCES таблица (колонка). И всегда проверяй результат через SHOW CREATE TABLE.

Опасный выключатель

У проверки есть выключатель, переменная foreign_key_checks. Её выключают, когда загружают резервную копию: таблицы приходят в произвольном порядке, и сеансы могут приехать раньше фильмов.

SET foreign_key_checks = 0;
INSERT INTO screenings (film_id, hall_id, starts_at, price) VALUES (99, 1, '2026-10-27 19:00:00', 400);
SET foreign_key_checks = 1;
SELECT id, film_id FROM screenings WHERE film_id = 99;
+----+---------+
| id | film_id |
+----+---------+
|  9 |      99 |
+----+---------+
1 row in set (0.00 sec)

Сеанс несуществующего фильма попал в базу. Включение проверки обратно ничего не исправило: MySQL проверяет только новые изменения, старые строки он не перечитывает. Такая висячая ссылка останется в базе, пока ты её не найдёшь сам. В обычной работе этот выключатель не трогай.

Что запомнить

  • Внешний ключ не даёт сослаться на строку, которой нет: ошибка 1452.
  • Он же не даёт удалить родителя, на которого ещё ссылаются: ошибка 1451.
  • ON DELETE CASCADE удаляет потомков вместе с родителем, SET NULL обнуляет ссылку.
  • Ключ ссылается на PRIMARY KEY или UNIQUE, а типы колонок должны совпадать.
  • REFERENCES прямо в описании колонки MySQL пропускает молча, ключ пиши отдельной строкой FOREIGN KEY.

Попробуй сам

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

Удали сеанс фильма 6, потом сам фильм, и посмотри, что стало с оценками. Попробуй удалить Анну Ковалёву (id = 1): почему не выйдет, хотя у оценок стоит SET NULL? Посмотри SHOW CREATE TABLE film_reviews\G и найди индексы, которые MySQL создал сам.

Проверь себя

Задание

Дальше

Внешний ключ охраняет связь между таблицами. Но и внутри одной таблицы есть что охранять: пустые имена, повторы email, отрицательные суммы. В следующем уроке разбираем NOT NULL, UNIQUE, DEFAULT и CHECK.

домашка

Домашка