Привет, первая запись в 2017 году! Пока все доедают салатики, я решил написать небольшую заметочку про ограничения внешних ключей в базе данных MySQL.
Зачем нужны внешние ключи в таблице
Внешние ключи регламентируют отношения между таблицами. Благодаря им сильно упрощается контроль за структурой базы, уменьшается и упрощается код приложения, потому что большая часть ответственности за это мы перекладываем со своих плеч на саму базу данных. Правильно настроенные внешние ключи — это гарант того, что увеличится целостность данных за счет уменьшения избыточности.
В двух словах — во внешних ключах много плюсов, поэтому полезно знать, как ими оперировать.
Настройка внешних ключей
Я буду демонстрировать настройку в своем любимом потому что это очень удобно, кроме того, админ не утаивает сгенерированный код запросов и вы всегда сможете его посмотреть(запомнить, скопировать, раскритиковать ).
Пример ManyToOne и OneToMany
Две таблицы: goods(id, name, catalog_id) и catalogs(id, name) . В одном каталоге множество товаров(OneToMany), и сколько угодно товаров может быть в одном каталоге(ManyToOne). Внешним ключом в этом примере является поле catalog_id в таблице goods .
Создание таблиц в phpmyadmin
Сгенерированные запросы
CREATE TABLEmybd.goods (id name VARCHAR(255) NOT NULL ,
catalog_id INT NOT NULL ,
PRIMARY KEY (id), INDEX (catalog_id)) ENGINE = InnoDB;
CREATE TABLE mybd.catalogs (id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR(255) NOT NULL ,
PRIMARY KEY (id)) ENGINE = InnoDB;
Настройка внешнего ключа catalog_id
Пришло время создать связь между товарами и каталогами. Открываем таблицу goods , переходим во вкладку «структура» и ищем кнопку «связи».
В разделе «ограничения внешнего ключа» заполняем строки и выбираем действия «ON DELETE» и «ON UPDATE».
Сгенерированный запрос
ALTER TABLEgoods ADD FOREIGN KEY (catalog_id)
REFERENCES mybd.catalogs(id) 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 TABLElibrary.author_book (author_id INT NOT NULL ,
book_id INT NOT NULL ,
INDEX (author_id, book_id)) ENGINE = InnoDB;
author_id и book_id вместе являются составным индексом, осталось только добавить ограничения на каждый из них во вкладке «связи» и все готово!
Заключение
Не упускайте возможность сложить побольше обязанностей на базу данных, чтобы облегчить себе работу над приложением, и думать о его структуре, а не о контроле табличный связей. Удачи с проектированием баз данных, спасибо за внимание!
phpMyAdmin содержит инструмент под названием Дизайнер . Этот инструмент позволяет упростить работу по созданию таблиц в базе данных и связей между таблицами.
Для начала создайте базу данных и войдите с полученными данными пользователя в phpMyAdmin .
Этап I. Создание хранилища конфигурации
Этап II. Работа с Дизайнером
- В левом поле со списком баз данных выберите вновь созданную
- Переходим в меню Ещё
и подменю Дизайнер
- В панели инструмента справа выбираем Показать/Скрыть список таблиц
- Слева откроется панель со списком таблиц. Скрыть/показать ненужные таблицы, относящиеся к Хранилищу настроек (pma)
, можно снятием/установкой соответствующей галочки
Создание связи
- Выбираем таблицы, между которыми необходимо настроить связи
- Нажимаем Создать связь
в панели инструментов
- Далее у стрелки появится надпись Выберите ссылочный ключ . Необходимо нажать на первичный ключ таблицы. В нашем случае это ключ tbl1_id в таблице table1
- Стрелка поменяет надпись на Выберите внешний ключ . Необходимо кликнуть на соответствующее поле связанной таблицы. В нашем случае это поле tbl1_id в таблице table2
- В диалоговом окне выбираем ОК
В этой заметке мы научимся создавать связи между таблицами в базе данных MySQL с помощью phpmyadmin . Если по какой-то причине вы не желаете использовать phpmyadmin , смотрите приведенные ниже SQL-запросы.
Почему же связи удобно держать в самой базе данных? Ведь эту задачу обычно решает так и само приложение? Все дело в ограничениях и действиях при изменении, которые можно наложить на связи.
Например, можно запретить удалять категорию, если с ней связана хотя б одна заметка. Или удалить все заметки, если удалена категория. Или установить NULL в связующее поле. В любом случае, с помощью связей повышается отказоустойчивость и надежность приложения.
Для начала, движок таблиц должен быть InnoDB . Только он поддерживает внешние ключи (foreign key ). Если у вас таблицы MyISAM , почитайте как их конвертировать в InnoDB .
Для того, чтобы связать таблицы по полям, необходимо сначала добавить в индекс связываемые поля:
В phpmyadmin выбираем таблицу, выбираем режим структуры, выделяем поле, для которого будем делать внешнюю связь и кликаем Индекс.
Обратите внимание на разницу между "Индекс" и "Уникальный". Уникальный индекс можно использовать, например, до поля id, то есть там, где значения не повторяются.
Это же действие можно сделать с помощью SQL -запроса:
ALTER TABLE `table_name` ADD INDEX (`field_name`) ;
Аналогично добавляем индекс (только в моем случае теперь уже уникальный или первичный) для таблицы, на которую ссылаемся, для поля id. Поскольку поле id у меня идентификатор, для него делаем первичный ключ. Уникальный ключ мог бы понадобится для других уникальных полей.
С помощью SQL
-запроса:
ALTER TABLE `table_name` ADD UNIQUE (`field_name`);
Теперь осталось только связать таблицы . Для этого кликаем внизу на пункт Связи:
Теперь для доступных полей (а доступны только проиндексированные поля) выбираем связь с внешними таблицами и действия при изменении записей в таблицах:
Через SQL
-запрос:
ALTER TABLE `table_name` ADD FOREIGN KEY (`field_in_table_name_which_need_connect`) REFERENCES `outer_table_to_connect` (`outer_field`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
на этом все, таблицы связаны через foreign key .
Здравствуйте, сегодня мы будем разбираться, как работать с таблицами в PHPMyAdmin
, и научимся
с Вами создавать, редактировать добавлять таблицы, а также рассмотрим их структуру. Для того чтобы добавить таблицу,
прежде всего необходимо создать БД, а уже следующий шаг является созданием таблицы.
Вначале необходимо зайти в созданную Вами БД, после чего перед вами появится следующее:
Где вы должны ввести название таблицы и количество столбцов, которые Вам понадобятся. Далее перед Вами должно появиться вот такое поле, где Вы должны будете указывать данные:
Первым делом, вы указываете имя поля, которое будет создано, например id вставляемой записи.
Далее указываете тип данных вашего поля. Конкретно обо всех типах мы поговорим отдельно.
Следующий шаг, Вы можете указать длину значения в символах. Можно также указывать значение, которое будет
назначено по умолчанию.
В сравнении Вы должны выбрать кодировку, однако нужно помнить, что ее следует задавать для строк,
т.е., если в нашем примере мы храним идентификатор записи, то и кодировку указывать не нужно.
В атрибутах рекомендую вам использовать
значение unsigned
, и применять его исключительно для чисел.
Если установить чекбокс Null
, то значение сможет его принимать,
т.е., оно может ничего в себе не содержать.
В следующем поле вы можете указать индекс, который Вам необходим: PRIMARY
, является первичным ключом и оно должно быть уникальным для всей
создаваемой таблицы. Как раз нашему полю ID, оно подойдет. UNIQUE
, если установить данное значение, то поле должно оставаться
уникальным, например логин пользователя. Значение INDEX
, задает индекс для указанного поля. И значение FullText
задает полнотекстовый
индекс для поля.
Если установить чекбокс A_I
, то при каждом добавлении новой записи в таблицу значение данного поля будет увеличиваться
на единицу автоматически.
В дальнейшем Вы можете указать свой комментарий к полю. Остальные параметры являются не очень
важными, поэтому мы разбирать их не будет, лишь отмечу еще одну вещь. В типе таблиц можете указывать InnoDB
или MyISAM
, а также не забывайте
задавать кодировку для таблицы. На этом мы может завершить создание таблицы и нажать ок. Все таблица создана и появилась у вас в левой части.
При клике на нее, у Вас должно появиться примерно такое меню:
Теперь Вы можете редактировать ваши поля, выбрав при этом нужное для Вас, и нажать кнопку изменить. Также можно удалить поле аналогичным способом, нажать кнопку удалить и подтвердить удаление. Если Вы вспомнили, что у Вас не хватает еще одного поля, то можете его добавить, указав при этом количество полей, после какого поля их вставлять и нажать ок. Вот я и перечислил основные манипуляции, выполняемые с таблицами. Вы можете еще немного посмотреть на все возможные настройки и возможности. На этом завершаем изучение работы с таблицами PHPMyAdmin , все, что я планировал, Вам уже рассказал. До скорого
