Внешние ключи

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

Когда я показывал LEFT JOIN, то без проблем смог вставить в таблицу телефонов запись с cityid равным 20, хотя реально такого города не существовало. База данных может защитить нас от таких глупостей.

А что, если мы попытаемся удалить город с номером 1 (Торонто)? Без защиты это можно сделать и тогда все телефоны, которые были созданы с указанием на город Торонто будут указывать в никуда.

Обе защиты можно реализовать с помощью одной и то же вещи – внешнего ключа.

Давайте вернемся в таблицу телефонов и сначала исправим ее. Сначала вставим в базу данных хотя бы одну строку, в которой cityId будет нереальный, например, 100.

INSERT INTO phone (firstname, cityid) values ('Bad Guy', 100);

Теперь попробуем создать связь между двумя таблицами:

ALTER TABLE phone ADD FOREIGN KEY (cityid) references city (cityid);

В Результате должна быть ошибка, которая не самая интеллектуальная, но все же она выглядит так:

ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`testdb`.`#sql-7c_f`, CONSTRAINT `#sql-7c_f_ibfk_1` 
FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

Даже переводить эту ошибку не буду, потому что она абсолютно непонятная и не отражает реальную проблему. А проблема в том, что мы создали запись с телефоном для города, которого не существует, а цель ключа как раз в том, чтобы защищать данные от подобных случаев, только корректные данные должны быть в базе. Исправим ситуацию, давайте изменим cityid на NULL для некорректной записи, которую мы только вставили:

UPDATE phone set cityid = null where cityid =100;

Повторяем попытку создать связь между таблицами:

ALTER TABLE phone ADD FOREIGN KEY (cityid) REFERENCES city (cityid);

На этот раз проблем не должно быть и есть смысл рассмотреть команду:

ПОДПРАВИТЬ ТАБЛИЦУ phone 
ДОБАВИТЬ ВНЕШНИЙ КЛЮЧ (колонка в таблице phone) 
ССЫЛАЕТСЯ другую таблицу (колонка в этой таблице);

Итак, мы добавляем внешний ключ. Что это такое? Это умное слово, которое на самом деле означает какую-то фигню, которая связывает две таблицы. Для нас главное понимать направление связи. Мы создаем внешний ключ для таблицы phone, и она ссылается на cityid, а не наоборот:

ALTER TABLE phone

Внешний ключ создается для колонки cityid в таблицы телефонов:

ALTER TABLE phone ADD FOREIGN KEY (cityid)

Мы ссылаемся на таблицу city:

ALTER TABLE phone ADD FOREIGN KEY (cityid) REFERENCES city

И в этой таблице city связь идет на колонку cityid.

ALTER TABLE phone ADD FOREIGN KEY (cityid) REFERENCES city (cityid);

Если заменить слово REFFERENCES на ->

ALTER TABLE phone ADD FOREIGN KEY (cityid) -> city (cityid);

И убрать все остальные служебные слова и пробелы, за исключением имен колонок и таблиц, то останется:

Phone(cityid) -> city(cityid)

Именно такое направление – cityid из телефонов ссылается на cityid в таблице city.

Почему направление так важно? Мы можем ссылаться только на уникальную колонку, но базы данных могут идти дальше и говорить, что ссылаться можно только на первичный ключ, который тоже по своей природе обеспечивает уникальность. Это уже вопрос реализации конкретной базы.

Колонка cityid является первичным ключом, поэтому на нее мы можем ссылаться. Колонка cityid в таблице телефонов – это просто число, там нет никакой уникальности, поэтому на него ссылаться нельзя.

А может уникальная колонка из одной таблицы ссылаться на уникальную колонку в другой таблицы? Может. Просто в этом случае связь будет один к одному.

Если у вас есть таблица городов:

+--------+-----------+
| cityid | cityname  |
+--------+-----------+
|      1 | Toronto   |
|      2 | Vancouver |
|      3 | Montreal  |
+--------+-----------+

И каких-то еще данных:

+--------+-----------+
| id     | name      |
+--------+-----------+
|      1 | Something |
|      2 | Another   |
|      3 | One more  |
+--------+-----------+

То вы сможете связать эти две таблицы по колонкам cityid -> id или в обратном направлении, это уже не имеет значения, потому что в обоих случаях ключ. Но в этом случае в обеих таблицах что-то уникальное и значит связь будет один к одному – одна запись в таблице городов соответствует одной записи в другой таблице. Вторую запись с уже существующим id вставить не получится, в том то и смысл уникальности и первичных ключей.

В нашем случае колонка cityid в таблицы телефонов – это просто число, и мы можем создать несколько телефонов с одним и тем же id города и у нас получается связь многие к одному – много телефонов в одном городе.

А можно сказать, что несколько телефонов принадлежать нескольким городам. Может же быть такое, что номер зарегистрирован в двух городах?

Или такой вариант – человек может обладать несколькими номерами или один номер принадлежит двум людям (мужу с женой). К сожаления напрямую внешними ключами такое реализовать нельзя, мы можем ссылаться только на уникальное значение, но чуть позже мы рассмотрим, как же можно обойти это ограничение и реализовать все же связь многие ко многим.

За счет того, что мы навели такую связь в нашей таблице обязательно должны быть только корректные id городов из таблицы city или несуществующие значения (NULL). Посмотрим, какие у нас есть города:

select * from city;
+--------+-----------+
| cityid | cityname  |
+--------+-----------+
|      1 | Toronto   |
|      2 | Vancouver |
|      3 | Montreal  |
+--------+-----------+

За счет установленной связи мы можем вставлять в таблицу телефонов записи с cityid одним из этих значений или NULL.

Попробуем вставить сейчас запись с cityid = 100:

INSERT INTO phone (firstname, cityid) values ('Bad Guy', 100);

До создания ключа эта операция закончилась успешно, а на этот раз она накрылось медным тазом, а точнее со следующей ошибкой?

ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`testdb`.`phone`, CONSTRAINT `phone_ibfk_1` 
FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

Попробуй перевести этот тест, потому что тут есть смысл:

Ошибка 1452 (23000): Не могу добавить или обновить дочернюю запись
Ошибка ограничения внешнего ключа (testdb.phone ограничение phone_idfk_1
ВНЕШНИЙ КЛЮЧ (cityid) ссылающийся на city (cityid))

Самая последняя строка очень похожа на ту команду, которую мы выполняли при создании внешнего ключа и именно его ограничение мы пытаемся нарушить, когда пытаемся вставить запись для города с id 100, а такого города не существует.

В city можно вставлять что угодно, потому что эта таблица ни на кого не ссылается, на нее ссылаются.

А что если попробовать удалить какой-то город из таблицы city:

delete from city where cityid = 1;

Снова ошибка:

1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`testdb`.`phone`, 
CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

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

delete from phone where cityid = 1;
delete from city where cityid = 1;

Я выполнять эти команды не буду, но поверьте мне, они выполнятся успешно, потому что после удаления всех телефонов с cityid равным 1, мы сможем удалить и город с этим id.

Итак, у нас получается два правила:

Если таблица ссылается НА ДРУГУЮ, то в связующую колонку (в нашем случае cityid в таблице phone) можно вставить только такое значение, которе есть в таблице city, иначе мы нарушаем правила связи. Удалять из этой таблицы можно любые записи.

Если ДРУГАЯ таблица ссылается на нашу, как это происходит с city, то в такую таблицу можно вставлять что угодно, мы связь не нарушим. А вот удалить можно только если мы не нарушаем связь, то есть в другой таблице нет записей с удаляемым id.

Давайте еще раз взглянем на ошибку:

1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`testdb`.`phone`, 
CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

В самой последней строке говориться CONSTRAINT `phone_ibfk_1`. Что за фигня, какое еще ограничение. Мы же ничего такого не создавали, мы создавали только внешний ключ. Проблема в том, что foreign key и есть ограничение constraint – ограничение на вставку и запись, о котором мы говорили выше. Мы просто использовали команду ADD FOREIGN KEY без указания имени ограничения и поэтому база данных выбрала такое странное имя phone_ibfk_1 за нас.

Если мы не указали имени, как его узнать? Тут все зависит от базы данных, для MySQL можно было бы выполнить команду:

SHOW CREATE TABLE phone;

В результате мы должны увидеть вот такой SQL:

CREATE TABLE `phone` (
  `phoneid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `cityid` int(11) DEFAULT NULL,
  PRIMARY KEY (`phoneid`),
  KEY `cityid` (`cityid`),
  CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

Опа, оказывается мы можем увидеть полный SQL создания таблицы! Причем в предпоследней строке указано ограничение внешнего ключа:

  CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)

Да, мы можем создавать внешний ключ прямо при создании таблицы при выполнении CREATE TABLE. Но для нас очень важно то, что мы можем так еще и увидеть название ограничения.

С помощью ALTER TABLE мы могли добавлять колонки

ALTER TABLE phone ADD колонка

И удалять:

ALTER TABLE phone DROP колонка

В этой части мы с помощью похожей команды добавили внешний ключ:

ALTER TABLE phone ADD FOREIGN KEY

Может мы и удалить можем с помощью:

ALTER TABLE phone DROP FOREIGN KEY

Да, можем, просто нужно передать после этого имя ограничения, которое мы хотим удалить:

ALTER TABLE phone DROP FOREIGN KEY phone_ibfk_1;

Если вы не хотите видеть имя ограничения по умолчанию, то его можно указать при создании связи. Просто нужно добавить после имени таблицы и перед FOREIGN KEY указать ADD CONSTRAINT имя_ограничения:

ALTER TABLE phone ADD CONSTRAINT fk_city FOREIGN KEY (cityid) REFERENCES city (cityid);

Проверяем, что получилось:

SHOW CREATE TABLE phone;

Результат:

CREATE TABLE `phone` (
  `phoneid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `cityid` int(11) DEFAULT NULL,
  PRIMARY KEY (`phoneid`),
  KEY `fk_city` (`cityid`),
  CONSTRAINT `fk_city` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Теперь в предпоследней строке запроса мы видим ограничение CONSTRAINT с именем, которое мы указали при создании.

Удалим это именованное ограничение:

ALTER TABLE phone DROP FOREIGN KEY fk_city;

Вернемся к правилам, которые поддерживают ключи. Если мы хотим удалить данные из таблицы, на которую ссылаются (из таблицы городов), то мы должны удалить все телефоны или изменить их id. То есть должны выполнить команды:

delete from phone where cityid = 1;
delete from city where cityid = 1;

или

update phone set cityid = null where cityid = 1;
delete from city where cityid = 1;

В первом перед удалением города я удаляю все телефоны, где id города равен 1, а во втором случае я меняю на null. Оба варианта работают, потому что мы делаем так, чтобы в таблице телефонов не осталось записей с нужным cityid на рушить связь.

Было бы круто, если бы база данных могла делать это за нас. И она может. Просто при создании внешнего ключа нужно указать, что мы хотим сделать со связанными данными – удалить или установить в null. Чтобы удалять, мы можем добавить к команде создания внешнего ключа три магических слова ON DELETE CASCADE – при удалении каскадно удалять и из связанных таблиц:

ALTER TABLE phone 
ADD CONSTRAINT fk_city 
FOREIGN KEY (cityid) REFERENCES city (cityid);
ON DELETE CASCADE

В этом случае при попытке выполнить команду удалении города, все записи телефонов с этим cityid будут так же удалены, то есть достаточно выполнить только одну команду:

DELETE FROM city WHERE cityid = 1;

Удалять из телефонов не нужно, произойдет все автоматически.

Чтобы обнулять выполняем команду создания внешнего ключа и добавляем ON DELETE SET NULL – при удалении устанавливать связанные записи в NULL:

ALTER TABLE phone 
ADD CONSTRAINT fk_city 
FOREIGN KEY (cityid) REFERENCES city (cityid);
ON DELETE SET NULL

Теперь при удалении записи из города:

DELETE FROM city WHERE cityid = 1;

реально будет автоматически выполняться:

UPDATE  phone SET cityid = null WHERE cityid = 1;
DELETE FROM city WHERE cityid = 1;

Стоит ли использовать ON DELETE SET NULL или ON DELETE SET DELETE? Лично я никогда не использую и вам не рекомендую. Весь смысл от внешнего ключа – это защита данных, чтобы вы не вставляли не существующие id городов и не удаляли города, для которых есть данные. Используя ON DELETE вы отключаете вторую защиту и если случайно попытаетесь удалить город, то будут удалены все телефоны и это может привести к потере данных. Если вы реально хотите удалить данные, то делайте это явно и не стоит возлагать эти вещи на базу данных.