SQL — Внешний ключ
От автора: внешний ключ SQL — это ключ, используемый для объединения двух таблиц. Иногда его также называют ссылочным ключом. Внешний ключ — это столбец или комбинация столбцов, значения которых соответствуют Первичному ключу в другой таблице.
Связь между двумя таблицами задается через соответствие Первичного ключа в одной из таблиц внешнему ключу во второй. Если для таблицы первичный ключ задан в определенном поле, то в этом поле не может содержаться двух записей с одинаковыми значениями.
Пример
Рассмотрим структуру следующих двух таблиц.
Первичный ключ и внешний ключ таблиц реляционных баз данных
В прошлой статье (устройство реляционной БД) мы разбирали, как устроена реляционная (табличная) база данных и выяснили, что основными элементами реляционной базы данных являются: таблицы, столбцы и строки, а в математических понятиях: отношения, атрибуты и кортежи. Также часто, строки называют записями, столбцы называют колонками, а пересечение записи и колонки называют ячейкой.
Важно вспомнить, что содержание строки и названия столбцов должны быть уникальны в пределах одной базы данных.
Типы данных в базах
Важно понимать, что можно создавать базы для любых типов данных: текстов, дат, времени, событий, цифр. В зависимости от типа информации реляционные базы данных делят на типы. Каждый тип данных (атрибут) имеет свое обозначение:
- INTEGER- данные из целых чисел;
- FLOAT – данные из дробных чисел, так называемые данные с плавающей точкой;
- CHAR, VARCHAR – текстовые типы данных (символьные);
- LOGICAL – логический тип данных (да/нет);
- DATE/TIME – временные данные.
Это основные типы данных, которых на самом деле гораздо больше. Причем, каждый язык программирования имеет свой набор системных атрибутов (типов данных).
Статьи по теме: Классификация баз данных
Что такое первичный ключ и внешний ключ таблиц реляционных баз данных
Первичный ключ
Выше мы вспоминали: каждая строка (запись) БД должна быть уникальна. Именно первичный ключ в виде наборов определенных значений, максимально идентифицируют каждую запись. Можно определить по-другому. Первичный ключ: набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ, как primary key.
Primary key (PK) очень важен для каждой таблицы. Поясню почему.
- Primary key не позволяет создавать одинаковых записей (строк) в таблице;
- PK обеспечивают логическую связь между таблицами одной базы данных (для реляционных БД).
На логической связи между таблицами, стоит остановиться подробнее.
Статьи по теме: Маршрутизация в компьютерных сетях
Ключ внешний
Foreign key, кратко FK. Обеспечивает однозначную логическую связь, между таблицами одной БД.
Например, есть две таблицы А и В. В таблице А (обувь), есть первичный ключ: размер, в таблице В (цвет) должна быть колонка с названием размер. В этой таблице «размер» это и будет внешний ключ для логической связи таблиц В и А.
Более сложный пример.
Две таблицы данных: Люди и Номера телефонов.
Таблица: Люди
| primary key | Имя |
| 1 | Зайцев |
| 2 | Белкин |
| 3 | Волков |
Таблица: Номера телефонов
| primary key | телефон | foreign key |
| 1 | 12345 | 1 |
| 2 | 54321 | 1 |
| 3 | 678910 | 2 |
| 4 | 109876 | 3 |
| 5 | 13579 | 3 |
В таблице Номера телефонов PK уникален. FK этой таблицы является PK таблицы Люди. Связь между номерами телефонов и людьми обеспечивает FK таблицы телефонов. То есть:
- У Зайцева два телефона;
- У Волкова два телефона;
- У Белкина один телефон.
Статьи по теме: SQL ALTER TABLE — sql запрос на модификацию таблицы базы данных
В завершении добавлю, что любая СУБД, управляющая базой данных, имеет технические возможности составить первичный ключ.
Другие статьи раздела: Базы данных
- PhpMyAdmin на локальном сервере
- Что такое база данных — понятие база данных в информатике
- Функции СУБД обеспечивающие управление базой данных
- Устройство реляционной базы данных
Зачем индексировать внешний / вторичный (foreign key) ключ в MySQL?
Есть понятие «ограничитель» в MySQL. Например, это первичный ключ (ключ должен быть уникален, поэтому каждый раз производится поиск «а не было ли этого ключа в предыдущих строках?» для ускорения которого автоматом столбец с первичным ключом индексируется) и ключ-кандидат (та же логика, что и с первичным ключом). То есть, есть логика: будет поиск каждый раз поиск, значит, надо проиндексировать. Теперь возьмем вторичный ключ. Он ссылается на таблицу-список. При создании новой строки в таблице надо проверить, а есть ли в таблице-списке этот вторичный ключ. То есть, вторичный ключ ограничивает. ВОПРОС: зачем индексировать вторичный ключ? Это же, например, столбец с номерами преподавателей (а в таблице-списке расшифровывается какой препод какому номеру соответствует). Зачем что-то искать в столбце вида «1,2,5,1,3,10. «?
Отслеживать
задан 23 июн 2018 в 14:08
2,049 3 3 золотых знака 18 18 серебряных знаков 35 35 бронзовых знаков
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Основная причина — возможность быстрой проверки на «осиротелые вторичные ключи» ( orphaned rows ) в случае удаления ( DELETE ) или смены ( UPDATE — лучше так не делать) значений первичного ключа.
id (PK) val 1 11 2 22 3 33 id (PK) master_id (FK) val 1 1 111 2 1 112 3 2 311 delete from master where >СУБД должна проверить - можно ли удалить PK == 3 для этого проверяются все таблицы содержащие вторичные ключи референцирующие столбец master.id и только если значение 3 не встречается ни в одном из вторичных ключей, тогда удаление разрешается.
Также существует возможность каскадного удаления (ON DELETE CASCADE) или перезаписи соответствующих значений вторичных ключей значением NULL (ON DELETE SET NULL) — в этих случаях нам тоже нужен быстрый (индексированный) доступ ко вторичным ключам.
Форум пользователей MySQL
CREATE TABLE product_order ( no INT NOT NULL AUTO_INCREMENT ,
product_category INT NOT NULL ,
product_id INT NOT NULL ,
customer_id INT NOT NULL ,
PRIMARY KEY ( no ) ,
INDEX ( product_category, product_id ) ,
FOREIGN KEY ( product_category, product_id )
REFERENCES product ( category, id )
ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE = INNODB ;
#3 20.05.2008 18:24:09
Nata Участник Зарегистрирован: 10.05.2008 Сообщений: 7
Re: Внешний ключ
А если в одной таблице 2-а столбца, оба являются внешними ключами на разные таблицы,тогда.
и ещё для чего после таблицы такая запись:ENGINE=INNODB?Почему-то MySQL ругается на эту запись.
#4 21.05.2008 00:34:46
LazY _cмельчак Зарегистрирован: 02.04.2007 Сообщений: 845
Re: Внешний ключ
FOREIGN KEY (имя_столбца) REFERENCES имя_родительской_таблицы(имя_родительского_столбца)
Про foreign key также полезно почитать http://webew.ru/posts/219.webew
для чего после таблицы такая запись:ENGINE=INNODB?Почему-то MySQL ругается на эту запись.
Это механизм хранения данных. MySQL поддерживает несколько механизмов хранения (см. http://dev.mysql.com/doc/refman/5.1/en/ … gines.html), но внешние ключи пока может только InnoDB.
Если ругается — проверьте еще раз, что все правильно написали.
#5 22.05.2008 07:22:47
Nata Участник Зарегистрирован: 10.05.2008 Сообщений: 7
Re: Внешний ключ
Пожалуйста, объясните в чем моя ошибка:
есть бд Poliklinika,таблицы:med_personal, в которой есть столбец id_mp-номер медперсонала; bolnye, где есть столбец id_bol-номер больного. Создаю такую табличку с 2-мя внешними ключами:
Create table bolnye_mp(
id_med_p int(4) not null,
id_boln int(4) not null,
primary key (id_med_p,id_boln),
CONSTRAINT FOREIGN KEY(id_med_p) REFERENCES med_personal (id_mp) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (id_boln) REFERENCES bolnye (id_bol) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
но почему-то ошибка:error 1005:Can’t create table ‘.\poliklinika\bolnye_mp.frm’ (errno:150)
Что я делаю не так?и что это за ошибка?
#6 22.05.2008 07:25:46
LazY _cмельчак Зарегистрирован: 02.04.2007 Сообщений: 845
Re: Внешний ключ
Так бывает, когда в дочерней таблице есть записи, которых нет в родительской (в т.ч. NULL’ы).
Попробуйте создать ключи на пустых таблицах, потом заполнить родительские и затем заполнить дочернюю.
А. Ну и еще родительские таблицы тоже InnoDB должны быть.
#7 06.06.2008 16:16:19
E-Stranger Участник Зарегистрирован: 06.06.2008 Сообщений: 5
Re: Внешний ключ
Не стал открывать новую тему, у меня тоже вопрос по внешнему ключу.
Задача стоит такая. Есть 2 таблицы:
Works( God , Qwartal , TypeOfWork , Summa)
и
Sprav( Element , Grup , Describe).
Домен поля TypeOfWork в первой таблице — это
Код:
SELECT Element FROM Sprav WHERE Grup = 'Work'
Напрашивается внешний ключ в первой таблице. Но, насколько я понимаю, не бывает таких столбцов, в которых были бы не значения, а запросы.
Поэтому сделал промежуточное представление WorkTypes с единственным столбцом TypeOfWork, образуемым вышеприведенным запросом. Представление создалось, в нем перечень неповторяющихся значений видов работ.
Но образование внешнего ключа с помощью конструкции вида
Код:
ALTER TABLE Works ADD FOREIGN KEY (TypeOfWork) REFERENCES WorkTypes(TypeOfWork) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can’t create table ‘.\diplom\#sql-4f8_1.frm’ (errno: 150)
В чем я ошибаюсь? И как правильно реализовать необходимую связку между двумя таблицами?
Отредактированно E-Stranger (06.06.2008 16:18:55)
#8 06.06.2008 16:45:34
rgbeast Администратор Откуда: Москва Зарегистрирован: 21.01.2007 Сообщений: 3877
Re: Внешний ключ
На VIEW к сожалению нельзя ссылаться внешним ключом. Такую функциональность, как Вы описываете напрямую реализовать нельзя, а разбивать основную таблицу на несколько будет не очень удобно. Создав триггер BEFORE UPDATE и BEFORE INSERT на ссылающейся таблице Вы не сможете запретить некорректную вставку (можно только имитировать поведение SET NULL)
#9 16.06.2008 10:08:44
E-Stranger Участник Зарегистрирован: 06.06.2008 Сообщений: 5
Re: Внешний ключ
rgbeast, а как же тогда быть? Неужели такой редко встречающийся случай? Имхо, это же довольно распространенная ситуация: есть справочник с перечнем, и при заполнении другой таблицы в определенном столбце можно выбирать только из вышереализованного перечня. Правда, в моем случае не один в один, перечень включает несколько групп, а нужен только из одной. Неужели этот нюанс так сильно затрудняет дело?
А может, создавать временные таблицы вместо представлений? Они автоматически создаются при входе в БД или их нужно заново создавать вручную при каждом входе в БД?
#10 16.06.2008 10:29:51
rgbeast Администратор Откуда: Москва Зарегистрирован: 21.01.2007 Сообщений: 3877
Re: Внешний ключ
То, что Вы говорите решилось бы, если бы на VIEW можно было бы вешать внешний ключ, но в MySQL на данный момент такого функционала нет.
Создайте не временные, а постоянные таблицы. Временные — живут в течение одной сессии пользователя (для каждого подключения отдельный набор). Не забывайте регенерировать все вторичные таблицы при изменении основной.
#11 17.06.2008 19:37:08
E-Stranger Участник Зарегистрирован: 06.06.2008 Сообщений: 5
Re: Внешний ключ
Создал таблицу на основе запроса:
Код:
CREATE TABLE IF NOT EXISTS WorkTypes SELECT Element FROM Sprav WHERE Grup = 'Work';
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
Имеем таблицу из одного столбца с неповторяющимися видами работ.
Далее попробовал создать внешний ключ:
Код:
ALTER TABLE Works ADD FOREIGN KEY (TypeOfWork) REFERENCES WorkTypes(Element) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can’t create table ‘.\diplom\#sql-50c_12.frm’ (errno: 150)
Что опять не так?
Уже дропнул таблицу Work (она не пустая, думал, может, из-за этого СУБД не дает создать внешний ключ). Но и попытка повторного создания таблицы с указанием внешнего ключа дает ошибку.
Я уже близок к тому, чтобы реализовать связи между таблицами не в БД, а в логике приложения, создавая вспомогательные таблицы в DataSet. Но там свои заморочки.
#12 17.06.2008 19:46:30
paulus Администратор Зарегистрирован: 22.01.2007 Сообщений: 6756
Re: Внешний ключ
Есть замечательная утилита — perror, она возвращает текстом то, что написано в коде ошибки.
$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
Для того, чтобы работали связи InnoDB, нужно, чтобы на таблицах были соответствующие
индексы. В данном случае, очевидно, не хватает
ALTER TABLE WorkTypes ADD INDEX (Element);
#13 18.06.2008 01:02:39
LazY _cмельчак Зарегистрирован: 02.04.2007 Сообщений: 845
Re: Внешний ключ
Да, действительно, нужен ключ на соотв. столбце родительской таблицы.
(на дочерней, кстати, должна сама создавать, если не указан)
#14 18.06.2008 22:55:17
E-Stranger Участник Зарегистрирован: 06.06.2008 Сообщений: 5
Re: Внешний ключ
LazY
Спасибо! Дело действительно было в индексе. Я их никогда не изучал особо (тем паче, что в курсе БД окромя первичных ключей ничего подобного не дают), считая за дополнительные цифровые столбцы, дублирующие первичный ключ. А это. оказывается, не дополнительные столбцы
Таким образом, связка между дочерней и промежуточной (для дочерней — родительской) таблицей реализуется. А между промежуточной и родительской, видимо, аналогичную связку делать в виде триггера? Три штуки и после изменения родительской таблицы?
Кстати, как правильно написать текст триггера? Просто внести аналогичное изменение в промежуточную таблицу? Или полностью написать код, создающий таблицу, прописывающий в нее индексы и добавляющий внешние ключи в дочернюю?
З.Ы. Не хочу заводить доп. тему, потому что больше уже не успею до защиты диплома задавать вопросы по другой теме. Как (если вообще можно) перенести БД с одной машины на другую? Простым копированием не переносится.
З.Ы. 2. Перрор не впечатлил. Про ошибку 1452 вообще ничего не смог сказать. Имхо, ничего нового к сообщениям утилиты mysql не дает.
#15 19.06.2008 15:02:18
paulus Администратор Зарегистрирован: 22.01.2007 Сообщений: 6756
Re: Внешний ключ
Ну, я тут, конечно, сбоку, но отвечу на ЗЫ
ЗЫРАЗ) MyISAM таблички проще всего копировать mysqlhotcopy, InnoDB — mysqldump + mysql.
ЗЫДВА) А Вы ничего и не писали про 1452
#16 19.06.2008 16:19:31
LazY _cмельчак Зарегистрирован: 02.04.2007 Сообщений: 845
Re: Внешний ключ
Триггер — не очень сложная вещь. Посмотрите документацию (там немного):
http://dev.mysql.com/doc/refman/5.0/en/ … igger.html
и у нас тут темы тоже были. Например, вот это сообщение:
http://sqlinfo.ru/forum/viewtopic.php?pid=4132#p4132
#17 20.06.2008 17:03:26
E-Stranger Участник Зарегистрирован: 06.06.2008 Сообщений: 5
Re: Внешний ключ
paulus, LazY, благодарю!
1452 вылезла позже, там ситуация была несколько другая. Пришлось по-другому разрулить, изменив структуру БД.