Работа с ограничениями внешних ключей
На этом последнем этапе мы рассмотрим, как ограничения внешних ключей помогают поддерживать целостность данных между связанными таблицами. Ограничения внешних ключей гарантируют, что отношения между таблицами остаются действительными, предотвращая операции, которые могут привести к созданию "сирот" (orphaned records) или несогласованным данным.
Попробуем понять, как работают ограничения внешних ключей на примерах:
Сначала попробуем добавить книгу с недействительным author_id
:
-- This will fail due to foreign key constraint
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);
Вы увидите сообщение об ошибке, потому что author_id
999 не существует в таблице authors
:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))
Это сообщение об ошибке показывает, что ограничение внешнего ключа для столбца author_id
в таблице books
не позволяет добавить книгу, которая ссылается на author_id
, не существующий в таблице authors
.
Аналогично, мы не можем удалить автора, который опубликовал книги, не обработав сначала его книги:
-- This will fail due to foreign key constraint
DELETE FROM authors WHERE author_id = 1;
Попытка удалить автора с author_id = 1
напрямую приводит к ошибке внешнего ключа, потому что есть книги, которые ссылаются на этот author_id
. Это способ MySQL гарантировать, что ваши данные остаются согласованными.
Чтобы безопасно удалить автора и его книги, нам нужно либо:
- Удалить сначала книги, а затем автора. Это гарантирует, что у нас не будет "сирот" (orphaned records).
- Использовать
CASCADE DELETE
(мы рассмотрим это в продвинутых лабораторных занятиях).
Посмотрим, как правильно удалить книгу и ее автора, удалив сначала книгу:
-- First, delete the books by this author
DELETE FROM books WHERE author_id = 1;
-- Now we can safely delete the author
DELETE FROM authors WHERE author_id = 1;
Удаяв первую книгу (или книги), вы удаляете ссылки внешнего ключа, что позволяет удалить автора без нарушения ограничений.