Создание внешних ключей онлайн урок.


Для создания внешнего ключа нам понадобится вторая таблица, которая будет некоторым образом связана с таблицей student. Пусть это будет таблица с информацией про группы, в которых состоят студенты.

CREATE TABLE `group` (
id TINYINT(2) AUTO_INCREMENT,
name VARCHAR (25),
description VARCHAR (50),
PRIMARY KEY (id))
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


Мы создали таблицу под названием group. Обратите внимание, что название таблицы записано в обратных кавычках ` `, поскольку group является зарезервированным словом синтаксиса SQL.

В эту таблицу запишем данные про 3 группы:

INSERT INTO `group` (name, description) VALUES
('ifka-33-M', 'Mathematics'),
('satr-41-P', 'Physics'),
('oskt-56-B', 'Biology');
+----+-----------+-------------+
| id | name | description |
+----+-----------+-------------+
| 1 | ifka-33-M | Mathematics |
| 2 | satr-41-P | Physics |
| 3 | oskt-56-B | Biology |
+----+-----------+-------------+
3 rows in set (0.00 sec)

Теперь нам надо привязать каждого студента к конкретной группе. Это значит, что в таблице student необходимо добавить новое поле - group_id, в котором будут сохранятся значения поля id таблицы group. Для этого необходимо:

  • поле group_id должно иметь точно такой же тип и те же атрибуты как и поле id таблицы group.
  • поле group_id должно быть индексом. Необходимо исполнить запрос:

ALTER TABLE student ADD INDEX(group_id);

Далее, при составлении запроса по добавлению внешнего ключа, на будет необходимо обращаться к разным полям разных таблиц. Для того, чтобы обратиться в запросе к полю конкретной таблицы, необходимо записать название таблицы и название поля и разделить их точкой, например student.id, `group`.descritpion.

Также необходимо отметить что внешний ключ поддерживается InnoDB таблицами, потому при необходимости потребуется привести таблицу к этому типу (см. предыдущие уроки).

Теперь перейдем к написанию запроса, который добавит внешний ключ в таблицу student. При написании этого запроса надо задать:

  • название связи (ограничения) - для того, чтобы потом к этой связи получить доступ при необходимости (изменение свойств, удаление): ADD CONSTRAINT название
  • какое поле будет внешним ключом: FOREIGN KEY поле
  • из какого поля берутся значения: REFERENCES родительское_поле
  • параметры целостности данных - какие действия необходимо осуществить СУБД при обновлении / удалении значения родительского поля: ON UPDATE ... ON DELETE ...

При этом, запрос начинается с предложения ALTER TABLE, т.к. это изменение таблицы.

ALTER TABLE student
ADD CONSTRAINT c_group_id
FOREIGN KEY (group_id)
REFERENCES `group`(id)
ON DELETE CASCADE
ON UPDATE CASCADE;


Тут мы назвали внешнюю связь c_group_id, обозначили поле group_id как внешний ключ, записали из какого поля будут подставляться значения и указали тип целостности CASCADE. Последнее означает, что при редактировании или удалении значений родительского поля - в поле с внешним ключом будут исполнятся те же изменения.

Теперь в поле group_id могут находится только те значения, которые есть в поле id таблицы student. Для того, чтобы не писать большое количество запросов для заполнения значений поля group_id (там сейчас значения NULL) таблицы student, заполним ее значениями в зависимости от возраста:

UPDATE student SET group_id = (age MOD 3) + 1;

тут мы использовали функцию MOD - деление с остатком. Получим следующую таблицу:

+----+---------+------+-----------+----------+
| id | name | age | phone | group_id |
+----+---------+------+-----------+----------+
| 1 | Mike | 25 | 345-65-78 | 2 |
| 2 | John | 27 | 123-65-12 | 1 |
| 4 | Andy | 20 | 244-34-12 | 3 |
| 5 | Debra | 21 | 987-65-43 | 1 |
| 6 | Andy | 24 | 323-43-98 | 1 |
| 7 | Mike | 23 | 313-54-21 | 3 |
| 8 | Jeff | 25 | 213-56-23 | 2 |
| 9 | Stanley | 23 | 111-23-43 | 3 |
+----+---------+------+-----------+----------+


Для создания внешней связи необходимо (после создания индекса для поля-внешнего ключа) перейти по ссылке Связи (Relation view).



Далее необходимо в колонке Foreign key constraint (INNODB) выбрать родительское поле для внешнего ключа, а также параметры целостности ON UPDATE и ON DELETE.



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

Проверьте свои знания
Создать новую таблицу continent, в которой будут сохранены названия континентов. Связать таблицу continent с таблицей country по полю continent_id при помощи внешнего ключа. После этого удалить из таблицы поле, которое явно указывает название континента.