Внешние ключи mysql что это
Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
Общий синтаксис установки внешнего ключа на уровне таблицы:
[CONSTRAINT имя_ограничения] FOREIGN KEY (столбец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE действие] [ON UPDATE действие]
Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers ( Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NOT NULL UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:
CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:
- CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
- SET NULL : при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL . (В этом случае столбец внешнего ключа должен поддерживать установку NULL)
- RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
- NO ACTION : то же самое, что и RESTRICT .
- SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.
Каскадное удаление
Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE :
CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE );
Подобным образом работает и выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );
FOREIGN KEY в SQL
FOREIGN KEY используется для ограничения по ссылкам. Когда все значения в одном поле таблицы представлены в поле другой таблицы, говорится, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей.
Когда одно поле в таблице ссылается на другое, оно называется внешним ключом; а поле на которое оно ссылается, называется родительским ключом. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми. Внешний ключ может иметь любое число полей, которые все обрабатываются как единый модуль. Внешний ключ и родительский ключ, на который он ссылается, должны иметь одинаковый номер и тип поля, и находиться в одинаковом порядке. Когда поле является внешним ключом, оно определенным образом связано с таблицей, на которую он ссылается. Каждое значение, (каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это условие соблюдается, то база данных находится в состоянии ссылочной целостности.
SQL поддерживает ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые можно ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа.
Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации структуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.
Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы позволяющей использовать многочисленные поля как один внешний ключ. Синтаксис ограничения таблицы FOREIGN KEY: FOREIGN KEY REFERENCES [ ] Первый список столбцов — это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой. Pktable — это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список столбцов — это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:
- иметь одинаковое число столбцов
- в данной последовательности, первый, второй, третий, и т.д., столбцы списка столбцов внешнего ключа, должны иметь одинаковые типы данных и размеры, что и первый, второй, третий, и т.д., столбцы списка столбцов родительского ключа.
- столбцы в списках обоих столбцов не должны иметь одинаковых имен.
FOREIGN KEY SQL. Пример 1
CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Kod_spec integer NOT NULL, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), FOREIGN KEY (Kod_spec) REFERENCES Spec (Kod_spec) );
При использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, указываемые во внешнем ключе и родительском ключе, должны быть в состоянии ссылочной целостности. Иначе команда будет отклонена.
Используя ограничение FOREIGN KEY таблицы или столбца, можно не указывать список столбцов родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двумя ключами все еще применим.
FOREIGN KEY. Пример 2
CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), Kod_spec integer REFERENCES Spec );
Поддержание ссылочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL).
Этого не достаточно для родительского ключа в случае выполнения такого требования, как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно необходимо убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.
Ссылка внешних ключей только на первичные ключи — хорошая стратегия. Когда используются внешние ключи, они связываются не просто с родительскими ключами, на которые они ссылаются; они связываются с определенной строкой таблицы, где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации, которая бы не была уже представлена во внешнем ключе.
Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа, который использует уникальный ключ как родительский ключ, необходимо создать внешний ключ, который бы использовал первичный ключ той же самой таблицы для того же самого действия. Внешний ключ, который не имеет никакой другой цели кроме связывания строк, напоминает первичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранения структуры базы данных ясной и простой. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или пустые (NULL). Попытка ввести другие значения в этот ключ будет отклонена.
FOREIGN KEY. Пример 3
CREATE TABLE payment ( sh_payout integer, sh_eml integer, date_payout date, summ_payout real, FOREIGN KEY (sh_eml) REFERENCES k_sotr2 (eid) );
В данном примере FOREIGN KEY столбец sh_eml связывается со столбцом eid из таблицы k_sotr2.
Основные ключевые слова, используемые в статье:
foreign key, foreign key mysql, foreign key oracle, foreign key sql, foreign key references
Внешние ключи MySQL (примеры)
Внешние ключи регламентируют отношения между таблицами. Благодаря им сильно упрощается контроль за структурой базы, уменьшается и упрощается код приложения, потому что большая часть ответственности за это мы перекладываем со своих плеч на саму базу данных. Правильно настроенные внешние ключи — это гарант того, что увеличится целостность данных за счет уменьшения избыточности. В двух словах — во внешних ключах много плюсов, поэтому полезно знать, как ими оперировать.
Пример ManyToOne и OneToMany
Дано: Две таблицы: goods(id, name, catalog_id) и catalogs(id, name) . В одном каталоге множество товаров (OneToMany), и сколько угодно товаров может быть в одном каталоге (ManyToOne). Внешним ключом в этом примере является поле catalog_id в таблице goods .
Создать таблицы можно следующими запросами:
CREATE TABLE mybd.goods ( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(255) NOT NULL , catalog_id INT NOT NULL , PRIMARY KEY (id), INDEX (catalog_id) ); CREATE TABLE mybd.catalogs ( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(255) NOT NULL , PRIMARY KEY (id) );
Создадим связь между товарами и каталогами с помощью внешнего ключа:
ALTER TABLE goods ADD FOREIGN KEY (catalog_id) REFERENCES mybd.catalogs(id) ON DELETE CASCADE ON UPDATE RESTRICT;
- 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.
Создание промежуточной таблицы связей:
CREATE TABLE library.author_book ( author_id INT NOT NULL , book_id INT NOT NULL , INDEX (author_id, book_id) );
author_id и book_id вместе являются составным индексом, осталось только добавить ограничения на каждый из них (см. выше).
Внешние ключи
До сих мы создавали таблицы и связи между ними представляли. Просто я не хотел усложнять примеры и при этом иметь возможность устанавливать любые значения и отключить защиту базы данных от неверных значений.
Когда я показывал 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);
И убрать все остальные служебные слова и пробелы, за исключением имен колонок и таблиц, то останется:
Именно такое направление – 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 вы отключаете вторую защиту и если случайно попытаетесь удалить город, то будут удалены все телефоны и это может привести к потере данных. Если вы реально хотите удалить данные, то делайте это явно и не стоит возлагать эти вещи на базу данных.