Внешний ключ

  • 17 августа 2017
  • 2156
0%

Помогло?

Чтобы лучше понять, что такое внешние ключи, лучше сначала смоделировать ситуацию: например, у нас есть 3 таблицы: пользователи, товары и заказы. Структура этих таблиц следующая:

Код на создание таблиц:

CREATE TABLE `orders` (
  `orderid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `producid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `products` (
  `prodid` int(11) NOT NULL,
  `name` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
  `userid` int(11) NOT NULL,
  `name` varchar(80) NOT NULL,
  `lastname` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Индексы таблицы `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`orderid`),
  ADD KEY `userid` (`userid`),
  ADD KEY `producid` (`producid`);
--
-- Индексы таблицы `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`prodid`);
--
-- Индексы таблицы `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userid`);
--
-- AUTO_INCREMENT для таблиц
-- ALTER TABLE `orders` MODIFY `orderid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0; ALTER TABLE `products` MODIFY `prodid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0; ALTER TABLE `users` MODIFY `userid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0;
--
-- Ограничения внешнего ключа таблицы `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`),
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`producid`) REFERENCES `products` (`prodid`);  

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

RESTRICT - данное правило устанавливается по умолчанию. Оно означает, что нельзя редактировать или удалять строки родительской таблицы. Если вы попробуете в данном примере создать пользователя и товар и внесёте данные в таблицу orders, то теперь у вас не получится удалить ни пользователя, ни товар. У вас будет выдаваться ошибка.

CASCADE - если вы отредактируете или удалите значения в родительской таблице, то эти данные изменятся автоматически и в дочерних таблицах. На нашем примере: если вы удалите товар, то из таблички orders удалятся все записи, которые содержали prodid этого товара.

Теперь давайте попробуем всё на практике. Занесем данные в табличку:

INSERT INTO `users` (`name`, `lastname`) VALUES ('Сергей', 'Иванов');
INSERT INTO `users` (`name`, `lastname`) VALUES ('Мария', 'Сергеева');
INSERT INTO `products` (`name`) VALUES ('Пылесос');
INSERT INTO `products` (`name`) VALUES ('Кофеварка');
INSERT INTO `orders` (`userid`, `producid`) VALUES ('1', '1');
INSERT INTO `orders` (`userid`, `producid`) VALUES ('1', '2');
INSERT INTO `orders` (`userid`, `producid`) VALUES ('2', '2');

Вот мы добавили данные, и созданные таблицы имеют связь по правилу RESTRICT. Давайте это проверим: попробуйте выполнить команду:

INSERT INTO `orders` (`userid`, `producid`) VALUES ('3', '1');

Вероятно, вам будет выдана ошибка:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`))

Так как пользователя с userid == 3 не существует, система выдает ошибку, тем самым сохраняя логику и целостность данных. По аналогии давайте попробуем удалить данные:

DELETE FROM `users` WHERE userid = 2

Не получается. Появляется та же самая ошибка.

Здесь всё понятно. Теперь давайте изменим наши внешние ключи на CASCADE

ALTER TABLE `orders` DROP FOREIGN KEY `orders_ibfk_1`; 
ALTER TABLE `orders` ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users`(`userid`) ON DELETE CASCADE ON UPDATE CASCADE; 
ALTER TABLE `orders` DROP FOREIGN KEY `orders_ibfk_2`; 
ALTER TABLE `orders` ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`producid`) REFERENCES `products`(`prodid`) ON DELETE CASCADE ON UPDATE CASCADE;

Обратите внимание, что через программу PHPmyAdmin вы можете создавать ограничения внешнего ключа из визуального интерфейса программы. Заходите в таблицу -> Структура -> Связи

Давайте попробуем с CASCADE. Сейчас у нас такие данные в таблице orders.

Давайте выполним команду и удалим товар с prodid == 2

DELETE FROM `products` WHERE prodid = 2

Запрос успешно выполнился. Теперь зайдем в таблицу orders. Вот, что мы там видим теперь:

Всего одна запись, а все связанные - удалились. Что важного можно отметить? Во-первых, вы можете быть уверены, что ваша система будет всегда целостной, а, значит, это значительно разгрузит бэкэнд (можно отказаться от лишних проверок). Во-вторых, благодаря внешним ключам, вы выполняете вместо 2-х запросов - один, (в случае CASCADE). Продумывайте архитектуру вашей системы, желаю удачи!

Облако тегов

Следующая статья

Сайт ищет хозяина – как продать выгодно?

Продать сайт – не проблема! Аукционы и профильные биржи позволяют в кратчайшие сроки найти покупателя и не прогадать с ценой. Спрос высок, как на раскрученные ресурсы, так и на новенькие сайты с небольшой посещаемостью. Ваше детище качественное? Значит, сможете его выгодно продать.

Сообщения: (0)

?
Публикуя сообщение, вы соглашаетесь с пользовательским соглашением

Читайте также