Привет, первая запись в 2017 году! Пока все доедают салатики, я решил написать небольшую заметочку про ограничения внешних ключей в базе данных MySQL.
Зачем нужны внешние ключи в таблице
Внешние ключи регламентируют отношения между таблицами. Благодаря им сильно упрощается контроль за структурой базы, уменьшается и упрощается код приложения, потому что большая часть ответственности за это мы перекладываем со своих плеч на саму базу данных. Правильно настроенные внешние ключи — это гарант того, что увеличится целостность данных за счет уменьшения избыточности.
В двух словах — во внешних ключах много плюсов, поэтому полезно знать, как ими оперировать.
Настройка внешних ключей
Я буду демонстрировать настройку в своем любимом php админе потому что это очень удобно, кроме того, админ не утаивает сгенерированный код запросов и вы всегда сможете его посмотреть(запомнить, скопировать, раскритиковать).
Пример ManyToOne и OneToMany
Две таблицы: goods(id, name, catalog_id) и catalogs(id, name). В одном каталоге множество товаров(OneToMany), и сколько угодно товаров может быть в одном каталоге(ManyToOne). Внешним ключом в этом примере является поле catalog_id в таблице goods.
Создание таблиц в phpmyadmin
Сгенерированные запросы
CREATE TABLE <code>mybd</code>.<code>goods</code> ( <code>id</code> INT NOT NULL AUTO_INCREMENT , <code>name</code> VARCHAR(255) NOT NULL , <code>catalog_id</code> INT NOT NULL , PRIMARY KEY (<code>id</code>), INDEX (<code>catalog_id</code>)) ENGINE = InnoDB; CREATE TABLE <code>mybd</code>.<code>catalogs</code> ( <code>id</code> INT NOT NULL AUTO_INCREMENT , <code>name</code> VARCHAR(255) NOT NULL , PRIMARY KEY (<code>id</code>)) ENGINE = InnoDB;
Настройка внешнего ключа catalog_id
Пришло время создать связь между товарами и каталогами. Открываем таблицу goods, переходим во вкладку «структура» и ищем кнопку «связи».
В разделе «ограничения внешнего ключа» заполняем строки и выбираем действия «ON DELETE» и «ON UPDATE».
Сгенерированный запрос
ALTER TABLE <code>goods</code> ADD FOREIGN KEY (<code>catalog_id</code>) REFERENCES <code>mybd</code>.<code>catalogs</code>(<code>id</code>) ON DELETE CASCADE ON UPDATE RESTRICT;
Значения ON DELETE и ON UPDATE
CASCADE — Каскадное удаление и редактирование. Эта настройка означает, что при удалении каталога, все товары из него тоже удалятся. При редактировании, если мы изменим id каталога, у товаров автоматически изменится поле «catalog_id».
RESTRICT — При этой настройке, если мы попытаемся удалить каталог, в котором есть товары, или изменить его id, база данных выдаст нам ошибку и удаление не состоится.
SET NULL — Из названия видно, что если исчезнет(удалится или изменится) каталог с таким id, то у товаров в поле «catalog_id» установится значение NULL. С этой настройкой нужно вести себя осторожно, потому что по умолчанию индексы «NOT NULL».
NO ACTION — Игнорируем удаление и редактирование каталога, и пусть в поле «catalog_id» будет несуществующий идентификатор, просто игнорируем это.
Пример ManyToMany
Случай посложнее, в нем, чтобы не дублировать записи, создают отдельную таблицу связей, которая обычно состоит из двух полей. Рассмотрим пример: authors(id, name), books(id, name), author_book(author_id, book_id). Книга может быть написана соавторами, и у автора может быть множество книг — отличный пример связи ManyToMany.
Создание таблицы связей
В phpmyadmin
Сгенерированный запрос
CREATE TABLE <code>library</code>.<code>author_book</code> ( <code>author_id</code> INT NOT NULL , <code>book_id</code> INT NOT NULL , INDEX (<code>author_id</code>, <code>book_id</code>)) ENGINE = InnoDB;
author_id и book_id вместе являются составным индексом, осталось только добавить ограничения на каждый из них во вкладке «связи» и все готово!
Заключение
Не упускайте возможность сложить побольше обязанностей на базу данных, чтобы облегчить себе работу над приложением, и думать о его структуре, а не о контроле табличный связей. Удачи с проектированием баз данных, спасибо за внимание!