Часто мы используем несколько таблиц в базе данных, чтобы соответствовать лучшим практикам нормализации. Среди обычных форм мы также должны обеспечить некоторую целостность связанных данных, распределенных между несколькими таблицами. Для этого можно использовать FOREIGN KEY вместе с определенными ограничениями…
Примечание: все данные, имена или наименования, найденные в базе данных, представленной в этом посте, строго используются для практики, обучения, обучения и тестирования. Он ни в коем случае не отображает фактические данные, принадлежащие или используемые какой-либо стороной или организацией.
Используемая ОС и БД:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.23
В этом сообщении блога мы рассмотрим использование FOREIGN KEY для обеспечения надежных методов работы с данными и ссылочной целостности между таблицами, содержащими связанные данные. Я смоделирую эти отношения, используя фиктивную «книгу» и сопроводительную таблицу «жанров».
Давайте посмотрим на подробное описание и состав таблиц для целевых таблиц, используя SHOW CREATE TABLE синтаксические запросы:
mysql> SHOW CREATE TABLE book\G *************************** 1. row *************************** Table: book Create Table: CREATE TABLE `book` ( `bk_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `genre_id` int(11) NOT NULL, PRIMARY KEY (`bk_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE book_genre\G *************************** 1. row *************************** Table: book_genre Create Table: CREATE TABLE `book_genre` ( `genre_id` int(11) NOT NULL, `genre_type` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec)
(Вы знакомы с командой SHOW? Взгляните на Примеры использования синтаксиса MySQL SHOW. Я писал об использовании этой удобной команды.)
Я запущу эти два исследовательских запроса, чтобы получить представление о текущих данных:
mysql> SELECT * FROM book; + — — — -+ — — — — — — — — — + — — — — — + | bk_id | title | genre_id | + — — — -+ — — — — — — — — — + — — — — — + | 1 | No Upper Limit | 202 | | 2 | The Lost Helm | 101 | | 3 | Golden Band | 303 | | 4 | 10th Grade Blues | 404 | | 5 | God Captain | 101 | + — — — -+ — — — — — — — — — + — — — — — + 5 rows in set (0.00 sec) mysql> SELECT * FROM book_genre; + — — — — — + — — — — — — -+ | genre_id | genre_type | + — — — — — + — — — — — — -+ | 101 | Fantasy | | 202 | Action | | 303 | Romance | | 404 | Young Adult | + — — — — — + — — — — — — -+ 4 rows in set (0.00 sec)
Предположим, мы сделали это INSERT на таблице book:
mysql> INSERT INTO book(title, genre_id)
-> VALUES (‘Blunt Spurs’, 505);
Query OK, 1 row affected (0.02 sec)
Хотя ошибок не произошло и снаружи все выглядит нормально, существует основная проблема. Таблица book_genre не имеет genre_id значения 505. Это не идеально и создает проблемы в поддержании согласованности данных и целостности нескольких таблиц. Один из способов предотвратить возникновение такого рода несоответствий в наших данных - использовать FOREIGN KEY.
Добавить или включить FOREIGN KEY в столбец таблицы в MySQL относительно просто. Однако есть несколько вещей, о которых нужно помнить, и вы увидите, когда я буду двигаться дальше. Прежде всего следует отметить, что FOREIGN KEY должен находиться в дочерней таблице. В данном примере это таблица book.
Поскольку таблица book уже существует, я воспользуюсь командой ALTER TABLE, чтобы добавить FOREIGN KEY:
mysql> ALTER TABLE book ADD CONSTRAINT FOREIGN KEY fk_bk_genre (genre_id) REFERENCES book_genre(genre_id) ON UPDATE CASCADE ON DELETE NO ACTION; ERROR 1215 (HY000): Cannot add foreign key constraint
Поиск дополнительной и полезной информации в сообществе
По общему признанию, я не понял сообщение об ошибке при запуске этой команды и обратился к документации за подсказками / смыслом. Я пришел к выводу, что причина в том, что указанный столбец в родительской таблице не является уникальным. По мере продвижения сообщения в блоге я исправляю это, делая столбец genre_id (ссылка) в таблице genre (родительская таблица) PRIMARY KEY. Что делает этот столбец уникальным. Кроме того, в документации (ссылка на которую приведена ниже) представляет интерес этот отрывок:
Взаимосвязи внешнего ключа включают родительскую таблицу, в которой хранятся центральные значения данных, и дочернюю таблицу с идентичными значениями, указывающими на ее родительскую.
Кроме того, в документации упоминается, что при добавлении FOREIGN KEY к существующей таблице с ALTER TABLE обязательно сначала создайте необходимые индексы.
Мы очень благодарны за любые исправления, информацию, идеи или дополнительные ресурсы, предоставленные читателями и сообществом.
Поддерживающие источники:
- Из официальной документации MySQL: 8.3.2 Оптимизация первичного ключа
- Из официальной документации MySQL: 13.1.18.6 Использование ограничений FOREIGN KEY
Как я упоминал выше, в этом операторе MySQL я делаю genre_id PRIMARY KEY для таблицы book_genre:
mysql> ALTER TABLE book_genre ADD CONSTRAINT PRIMARY KEY pk_genre_id (genre_id); Query OK, 0 rows affected (0.65 sec) Records: 0 Duplicates: 0 Warnings: 0
Давайте еще раз быстро просмотрим описание этой таблицы и увидим, что PRIMARY key на месте:
mysql> SHOW CREATE TABLE book_genre\G *************************** 1. row *************************** Table: book_genre Create Table: CREATE TABLE `book_genre` ( `genre_id` int(11) NOT NULL, `genre_type` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`genre_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.02 sec)
Теперь, чтобы добавить FOREIGN KEY в таблицу book:
mysql> ALTER TABLE book ADD CONSTRAINT FOREIGN KEY fk_bk_genre (genre_id) REFERENCES book_genre(genre_id) ON UPDATE CASCADE ON DELETE NO ACTION; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`#sql-613_a0`, CONSTRAINT `#sql-613_a0_ibfk_1` FOREIGN KEY (`genre_id`) REFERENCES `book_genre` (`genre_id`) ON DELETE NO ACTION ON UPDATE CASCADE)
Прежде чем объяснять, что означает указанная выше ошибка, давайте вернемся к данным в таблице book, поскольку в этом заключается проблема:
mysql> SELECT * FROM book; + — — — -+ — — — — — — — — — + — — — — — + | bk_id | title | genre_id | + — — — -+ — — — — — — — — — + — — — — — + | 1 | No Upper Limit | 202 | | 2 | The Lost Helm | 101 | | 3 | Golden Band | 303 | | 4 | 10th Grade Blues | 404 | | 5 | God Captain | 101 | | 6 | Blunt Spurs | 505 | + — — — -+ — — — — — — — — — + — — — — — + 6 rows in set (0.00 sec)
Напомним, что genre_id значение столбца 505 в таблице book_genre отсутствует. И вот, проблема обнаружена. (См. Процитированный отрывок из вышеупомянутой документации относительно идентичных данных между родительской и дочерней таблицами).
Чтобы исправить ошибку, я INSERT это значение 505 с соответствующим genre_type:
mysql> INSERT INTO book_genre(genre_id, genre_type)
-> VALUES (505, ‘Western’);
Query OK, 1 row affected (0.04 sec)
Наконец, мы можем создать FOREIGN KEY в таблице book:
mysql> ALTER TABLE book ADD CONSTRAINT FOREIGN KEY fk_bk_genre (genre_id) REFERENCES book_genre(genre_id) ON UPDATE CASCADE ON DELETE NO ACTION; Query OK, 6 rows affected (0.68 sec) Records: 6 Duplicates: 0 Warnings: 0
Установив взаимосвязь между PRIMARY KEY таблицы book_genre и FOREIGN KEY в таблице book, теперь у нас есть средства для обеспечения ссылочной целостности вместе со сплошным столбцом, который можно использовать для связывания совпадающих строк с помощью JOIN.
Вот пример запроса, возвращающего эти книги в жанре 'Fantasy':
mysql> SELECT b.title, bk.genre_type
-> FROM book AS b
-> INNER JOIN
-> book_genre AS bk
-> USING(genre_id)
-> WHERE bk.genre_type = ‘Fantasy’;
+ — — — — — — — -+ — — — — — — +
| title | genre_type |
+ — — — — — — — -+ — — — — — — +
| The Lost Helm | Fantasy |
| God Captain | Fantasy |
+ — — — — — — — -+ — — — — — — +
2 rows in set (0.04 sec)
В дополнение к столбцу-кандидату для присоединения отношение FOREIGN KEY запрещает те INSERT ', у которых нет совпадающего значения в родительской таблице. Как в этом примере:
mysql> INSERT INTO book(title, genre_id)
-> VALUES(‘Broken Scope’, 797);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`book`, CONSTRAINT `book_ibfk_1` FOREIGN KEY (`genre_id`) REFERENCES `book_genre` (`genre_id`) ON DELETE NO ACTION ON UPDATE CASCADE)
Вышеупомянутый INSERT завершился неудачно, поскольку предоставленный genre_id из 797 не существует в таблице book_genre.
Следует отметить, что FOREIGN KEY также может быть установлен в начальной команде CREATE TABLE.
Дополнительная литература и ресурсы
- Из официальной документации: 13.1.18.6 Использование ограничений FOREIGN KEY
- Ограничение SQL FOREIGN KEY
- Внешний ключ MySQL
Я надеюсь, что это сообщение в блоге предоставило вам достаточно информации и, что более важно, любопытство, чтобы узнать больше о FOREIGN KEY. Однако мы еще не закончили. В следующем сообщении блога я расскажу о ссылочных действиях (например, ON UPDATE CASCADE), указанных в примерах, приведенных в этом сообщении блога. Обязательно посетите этот пост, когда он будет опубликован. Я с нетерпением жду любых комментариев, которые у вас есть ниже, и большое спасибо за чтение !!!
Изучите официальное MySQL 5.7 Online Manual для получения дополнительной информации.
Призыв к действию!
Спасибо, что нашли время прочитать этот пост. Я искренне надеюсь, что вы открыли для себя что-то интересное и поучительное. Пожалуйста, поделитесь своими выводами здесь с кем-нибудь, кого вы знаете, кто тоже получит от этого такую же ценность.
Посетите страницу Портфолио-проекты, чтобы увидеть сообщения в блоге / технические статьи, которые я написал для клиентов.
Я уже упоминал, как я люблю чашку кофе?!?!
Чтобы получать уведомления по электронной почте (Никогда не спамить) от этого блога (Проза Цифровой Совы) о последних публикациях в блоге по мере их публикации, пожалуйста, подпишитесь (по собственному желанию), нажав кнопку Нажмите, чтобы подписаться! На странице боковая панель на главной странице! (Не стесняйтесь в любое время просмотреть страницу политики конфиденциальности Digital Owl's Prose по любым вопросам, которые могут у вас возникнуть: обновления по электронной почте, подписка, отказ, контактные формы и т. Д.)
Обязательно посетите страницу Best Of, где собраны мои лучшие сообщения в блоге.
Джош Отвелл хочет учиться и расти как разработчик SQL и блогер. Другие любимые занятия находят его, уткнувшись носом в хорошую книгу, статью или командную строку Linux. Среди них он разделяет любовь к настольным ролевым играм, чтению фэнтезийных романов и проведению времени с женой и двумя дочерьми.
Отказ от ответственности: примеры, представленные в этом посте, представляют собой гипотетические идеи о том, как достичь аналогичных результатов. Это не самое лучшее решение. Большинство, если не все, из представленных примеров выполняются в среде рабочей станции для личного развития / обучения и не должны считаться производственными или готовыми к использованию. Ваши конкретные цели и потребности могут отличаться. Используйте те методы, которые лучше всего подходят для ваших нужд и целей. Мнения мои собственные.
Первоначально опубликовано на joshuaotwell.com 12 сентября 2018 г.
✉️ Подпишитесь на рассылку еженедельно Email Blast от CodeBurst 🐦 Подпишитесь на CodeBurst на Twitter , просмотрите 🗺️ Дорожная карта веб-разработчиков на 2018 год и 🕸️ Изучите веб-разработку с полным стеком .
