Внешний ключ: MySQL | Внешние ключи FOREIGN KEY

Реляционные базы данных | Внешние ключи и связи

Внешние ключи и связи

Последнее обновление: 02.07.2017

Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (relationship) представляет ассоциацию между
сущностями разных типов.

При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table).
Дочерняя таблица зависит от родительской.

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

Связи между таблицами бывают следующих типов:

• Один к одному (One to one)

• Один к многим (One to many)

• Многие ко многим (Many to many)

Связь один к одному

Данный тип связей встречает не часто. В этом случае объекту одной сущности можно сопоставить только один объект другой сущности. Например,
на некоторых сайтах пользователь может иметь только один блог. То есть возникает отношение один пользователь — один блог.

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

В этом отношении первичный ключ зависимой таблицы в то же время является внешним ключом, который ссылается на первичный ключ из главной таблицы.

Например, таблица Users представляет пользователей и имеет следующие столбцы:

• UserId (идентификатор, первичный ключ)

• Name (имя пользователя)

И таблица Blogs представляет блоги пользователей и имеет следующие столбцы:

• BlogId (идентификатор, первичный и внешний ключ)

• Name (название блога)

В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.

Связь один ко многим

Это наиболее часто встречаемый тип связей. В этом типе связей несколько строк из дочерний таблицы зависят от одной строки в
родительской таблице. Например, в одном блоге может быть несколько статей. В этом случае таблица блогов является родительской, а таблица статей — дочерней.
То есть один блог — много статей. Или другой пример, в футбольной команде может играть несколько футболистов. И в то же время один футболист одновременно
может играть только в одной команде. То есть одна команда — много футболистов.

К примеру, пусть будет таблица Articles, которая представляет статьи блога и которая имеет следующие столбцы:

• ArticleId (идентификатор, первичный ключ)

• BlogId (внешний ключ)

• Title (название статьи)

• Text (текст статьи)

В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.

Связь многие ко многим

При этом типе связей одна строка из таблицы А может быть связана с множеством строк из таблицы В. В свою очередь одна строка из таблицы В может быть связана с множеством строк
из таблицы А. Типичный пример — студенты и курсы: один студент может посещать несколько курсов, и соответственно на один курс могут записаться несколько студентов.

Другой пример — статьи и теги: для одной статьи можно определить несколько тегов, а один тег может быть определен для нескольких статей.

Но в SQL Server на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством
вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность.

Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:

• TagId (идентификатор, первичный ключ)

• Text (текст тега)

Также пусть будет промежуточная таблица ArticleTags со следующими полями:

• TagId (идентификатор, первичный и внешний ключ)

• ArticleIdId (идентификатор, первичный и внешний ключ)

Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags.
А столбец ArticleId из таблицы ArticleTags будет ссылаться
на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и
одновременно являются внешними ключами для связи с таблицами Articles и Tags.

Ссылочная целостность данных

При изменении первичных и внешних ключей следует соблюдать такой аспект как ссылочная целостность данных (referential integrity).
Ее основная идея состоит в том, чтобы две таблице в базе данных, которые хранят одни и те же данные, поддерживали их согласованность.
Целостность данных представляет правильно выстроенные отношения между таблицами с корректной установкой ссылок между ними. В каких случаях целостность данных может нарушаться:

• Аномалия удаления (deletion anomaly). Возникает при удалении строки из главной таблицы.
  В этом случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы

• Аномалия вставки (insertion anomaly). Возникает при вставке строки в зависимую таблицу.
  В этом случае внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы.

• Аномалии обновления (update anomaly). При подобной аномалии несколько строк одной таблицы могут содержать данные, которые принадлежат одному и
  тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки.

Аномалия удаления

Для решения аномалии удаления для внешнего ключа следует устанавливать одно из двух ограничений:

• Если строка из зависимой таблицы обязательно требует наличия строки из главной таблицы,
  то для внешнего ключа устанавливается каскадное удаление. То есть при удалении строки из главной таблицы происходит удаление связанной строки (строк) из
  зависимой таблицы.

• Если строка из зависимой таблицы допускает отсутствие связи со строкой из главной таблицы (то есть такая связь необязательна),
  то для внешнего ключа при удалении связанной строки из главной таблицы задается установка значения NULL. При этом столбец внешнего ключа должен допускать
  значение NULL.

Аномалия вставки

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

Аномалии обновления

Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.

1.2.5. Первичный и внешний ключ

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

1.2.5. Первичный ключ

Мы уже достаточно много говорили про ключевые поля, но ни разу их не использовали. Самое интересное, что все работало. Это преимущество, а может недостаток базы данных Microsoft SQL Server и MS Access. В таблицах Paradox такой трюк не пройдет и без наличия ключевого поля таблица будет доступна только для чтения.

В какой-то степени ключи являются ограничениями, и их можно было рассматривать вместе с оператором CHECK, потому что объявление происходит схожим образом и даже используется оператор CONSTRAINT. Давайте посмотрим на этот процесс на примере. Для этого создадим таблицу из двух полей «guid» и «vcName». При этом поле «guid» устанавливается как первичный ключ:

CREATE TABLE Globally_Unique_Data
(
 guid uniqueidentifier DEFAULT NEWID(),
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (Guid)
)

Самое вкусное здесь это строка CONSTRAINT. Как мы знаем, после этого ключевого слова идет название ограничения, и объявления ключа не является исключением. Для именования первичного ключа, я рекомендую использовать именование типа PK_имя, где имя – это имя поля, которое должно стать главным ключом. Сокращение PK происходит от Primary Key (первичный ключ).

После этого, вместо ключевого слова CHECK, которое мы использовали в ограничениях, стоит оператор PRIMARY KEY, Именно это указывает на то, что нам необходима не проверка, а первичный ключ. В скобках указывается одно, или несколько полей, которые будут составлять ключ.

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

В данном примере, в качестве первичного ключа выступает поле типа uniqueidentifier (GUID). Значение по умолчанию для этого поля – результат выполнения серверной процедуры NEWID.

Внимание

Только один первичный ключ может быть создан для таблицы

Для простоты примеров, в качестве ключа желательно использовать численный тип и если позволяет база данных, то будет лучше, если он будет типа «autoincrement» (автоматически увеличивающееся/уменьшающееся число). В MS SQL Server таким полем является IDENTITY, а в MS Access это поле типа «счетчик».

Следующий пример показывает, как создать таблицу товаров, в которой в качестве первичного ключа выступает целочисленное поле с автоматическим увеличением:

CREATE TABLE Товары
(
  id int IDENTITY(1, 1),
  товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY (id)
)

Именно такой тип ключа мы будем использовать чаще всего, потому что в ключевом поле будут храниться легкие для восприятия числа и с ними проще и нагляднее работать.

Первичный ключ может состоять из более, чем одной колонки. Следующий пример создает таблицу, в которой поля «id» и «Товар» образуют первичный ключ, а значит, будет создан индекс уникальности на оба поля:

CREATE TABLE Товары1
(
  id int IDENTITY(1, 1),
  Товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY 
         (id, [Название товара])
)

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

Единственный недостаток первичного ключа из нескольких колонок – проблемы создания связей. Тут приходиться выкручиваться различными методами, но проблема все же решаема. Достаточно только ввести поле типа uniqueidentifier и производить связь по нему. Да, в этом случае у нас получаются уникальными первичный ключ и поле типа uniqueidentifier, но эта избыточность в результате не будет больше, чем та же таблица, где первичный ключ uniqueidentifier, а на поля, которые должны быть уникальными установлено ограничение уникальности. Что выбрать? Зависит от конкретной задачи и от того, с чем вам удобнее работать.

1.2.6. Внешний ключ

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

• Names – содержит имена людей и состоит из полей идентификатора (ключевое поле), имя.
• Phones – таблица телефонов, которая состоит из идентификатора (ключевое поле), внешний ключ для связи с таблицей names и строковое поле для хранения номера телефона.

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

Рис. 1.4. Связь между таблицами

Для примера возьмем таблицу из трех человек. В таблице 1.3 показано содержимое таблицы «Names». Здесь всего три строки и у каждой свой уникальный главный ключ. Для уникальности, когда будем создавать таблицу, сделаем ключ автоматически увеличиваемым полем.

Таблица 1.3 Содержимое таблицы Names

Таблица 1.4. Содержимое таблицы Phones

В таблице 1.4 находится пять номеров телефонов. В поле главный ключ также уникальный главный ключ, которой также можно сделать автоматически увеличиваемым. Вторичный ключ – это связь с главным ключом таблицы Names. Как работает эта связь? У Петрова в таблице Names в качестве главного ключа стоит число 1. В таблице Phones во вторичном ключе ищем число 1 и получаем номера телефонов Петрова. То же самое и с остальными записями. Визуально связь можно увидеть на рисунке 1.5.

Рис. 1.5. Связь между строками табли

Такое хранение данных очень удобно. Если бы не было возможности создавать связанные таблицы, то в таблице Names пришлось бы забивать все номера телефонов в одно поле. Это неудобно с точки зрения использования, поддержки и поиска данных.

Можно создать в таблице несколько полей Names, но возникает вопрос – сколько. У одного человека может быть только 1 телефон, а у меня, например, их 3, не считая рабочих. Большое количество полей приводит к избыточности данных.

Можно для каждого телефона в таблице Names заводить отдельную строку с фамилией, но это легко только для такого простого примера, когда нужно вводить только фамилию и легко можно внести несколько записей для Петрова с несколькими номерами телефонов. А если полей будет 10 или 20?
Итак, создание двух таблиц связанных внешним ключом можно увидеть в листинге 1.6.

Листинг 1.6. Создание таблиц связанных внешним ключом

CREATE TABLE Names
(
 idName int IDENTITY(1,1), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName),
)

CREATE TABLE Phones
(
 idPhone int IDENTITY(1,1), 
 idName int, 
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

Внимательно изучите содержимое листинга. Он достаточно интересен, потому что использует некоторые операторы, которые мы уже рассмотрели и дополнительный пример не помешает. Для обеих таблиц создается ключевое поле, которое стоит первым, имеет тип int и автоматически увеличивается, начиная с 1 с приращением в единицу. Ключевое поле делается главным ключом с помощью ограничение CONSTRAINT.

В описании таблицы Phones последняя строка содержит новое для нас объявление, а именно – объявление внешнего ключа с помощью оператора FOREIGN KEY. Как видите, это тоже ограничение и чуть позже вы увидите почему. В скобках указывается поле таблицы, которое должно быть связано с другой таблицей. После этого идет ключевое слово REFERENCES (ссылка), имя таблицы, с которой должна быть связь (Names) и в скобках имя поля («idName»). Таким образом, мы навели связь, которая отображена на рисунке 1.4.

Внимание!

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

Теперь, если можно наполнять таблицы данными. Следующие три команды добавляют три фамилии, которые мы видели в таблице 1.3:

INSERT INTO Names(vcName) 
VALUES('Петров')

INSERT INTO Names(vcName) 
VALUES('Иванов')

INSERT INTO Names(vcName) 
VALUES('Сидоров')

Если вы уже работали с SQL то сможете добавить записи и для таблицы телефонов. Я опущу эти команды, а вы можете увидеть их в файле foreign_keys.sql директории Chapter1 на компакт диске.

Наша задача сейчас увидеть, в чем заключаются ограничительные действия внешнего ключа, давайте разберемся. Мы указали явную связь между двумя полями в разных таблицах. Если попытаться добавить в таблицу телефонов запись с идентификатором в поле «idName», не существующим в одноименном поле (имя можно было сделать и другим) таблице с фамилиями, то произойдет ошибка. Это нарушит связь между двумя таблицами, а ограничение внешнего ключа не позволит существовать записям без связи.

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

Во время создания внешнего ключа, можно указать ON DELETE CASCADE или ON UPDATE CASCADE. В этом случае, если удалить запись Петрова из таблице Names или изменить идентификатор, то все записи в таблице Phones, связанные со строкой Петрова будут автоматически обновлены. Никогда. Нет, нужно написать большими буквами: НИКОГДА не делайте этого. Все должно удаляться или изменяться вручную. Если пользователь случайно удалит запись из таблицы Names, то удаляться и соответствующие телефоны. Смысл тогда создавать внешний ключ, если половина его ограничительных возможностей исчезает! Все необходимо делать только вручную, а идентификаторы изменять не рекомендуется вообще никогда.

Удаление самих таблиц также должно начинаться с подчиненной таблицы, то есть с Phones, и только потом можно удалить главную таблицу Names.

Напоследок покажу, как красиво получить соответствие имен и телефонов из двух таблиц:

SELECT vcName, vcPhone 
FROM Names, Phones
WHERE Names.idName=Phones.idName

Более подробно о подобных запросах мы поговорим в главе 2. Сейчас же я привел пример только для того, чтобы вы увидели мощь связанных таблиц.

Таблица может содержать до 253 внешних ключей, что вполне достаточно даже для построения самых сложных баз данных. Лично мне приходилось работать с базами данных, где количество внешних ключей не превышало 7 на одну таблицу. Если больше, то скорей всего база данных спроектирована неверно, хотя бывают и исключения.

Сама таблица также может иметь максимум 253 внешних ключей. Внешние ключи в таблице встречаются реже, в основном не более 3. Чаще всего в таблице может быть много ссылок на другие таблицы.

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

Таблица 1.5. Таблица с внутренней связью

В результате мы получаем, что у генерального директора внешний ключ нулевой, т.е. эта должность стоит во главе всех остальных. У коммерческого директора и директора по общим вопросам внешний ключ указывает на строку генерального директора. Это значит, что эти две должности подчиняются непосредственно генеральному директору. И так далее.

Посмотрим, как можно создать все это в виде SQL запроса:

CREATE TABLE Positions
(
 idPosition int IDENTITY(1,1), 
 idParentPosition int, 
 vcName varchar(30),  
 CONSTRAINT PK_idPosition PRIMARY KEY (idPosition),
 CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition)
   REFERENCES Positions (idPosition)
)

Как видите, внешний ключ просто ссылается на ту же таблицу, которую мы создаем. На компакт диске, в директории Chapter1 можно увидеть в файле foreign_keys_to_self.sql пример создания этой таблицы, наполнения его данными и отображения должностей с учетом их подчинения. В следующей главе мы рассмотрим возможность работы с такими таблицами более подробно.

Отношение один к одному

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

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

 
CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Names (idName)
)

Внешний ключ нужен только у одной из таблиц. Так как связь идет один к одному, то не имеет значения, в какой таблице создать его.

Многие ко многим

Самая сложная связь – многие ко многим, когда много записей из одной таблицы соответствует многим записям из другой таблицы. Чтобы такое реализовать, двух таблиц мало, необходимо три таблицы.

Для начала нужно понять, когда может использоваться связь многие ко многим? Допустим, что у вас есть две таблицы: список жителей дома и список номеров телефона. В одной квартире может быть более одного номера, а значит, одной фамилии может принадлежать два телефона. Получается, связь один ко многим. С другой стороны, в одной квартире может быть две семьи (коммунальная квартира или просто квартиросъемщик, который пользуется телефоном владельца), а значит, связь между телефоном и жителем тоже один ко многим. И самый сложный вариант – в коммунальной квартире находиться два телефона. В этом случае обоими номерами пользуются несколько жителей квартире. Вот и получается, что «много» семей может пользоваться «многими» телефонами (связь многие ко многим).

Как реализовать связь многие ко многим? На первый взгляд, в реляционной модели это невозможно. Лет 10 назад я долго искал разные варианты и в результате просто создавал одну таблицу, которая была переполнена избыточностью данных. Но однажды, мне досталась одна задача, благодаря которой уже из условия на поверхность вышло отличное решение – нужно создать две таблицы жителей квартир и телефонов и реализовать в них только первичный ключ. Внешние ключи в этой таблице не нужны. А вот связь между таблицами должна быть через третью, связующую таблицу. На первый взгляд это сложно и не понятно, но один раз разобравшись с этим методом, вы увидите всю мощь этого решения.

В таблицах 1.6 и 1.7 показаны примеры таблиц фамилий и телефонов соответственно. А в таблице 1.8 показана связующая таблица.

Таблица 1.6. Таблица фамилий

Таблица 1.7. Таблица телефонов

Таблица 1.8. Таблица телефонов

Давайте теперь посмотрим, какая будет логика поиска данных при связи многие ко многим. Допустим, что нам нужно найти все телефоны, которые принадлежат Иванову. У Иванова первичный ключ равен 1. Находим в связующей таблице все записи, у которых поле «Связь с именем» равно 1. Это будут записи 1 и 2. В этих записях в поле «Связь с телефоном» находятся идентификаторы 1 и 2 соответственно, а значит, Иванову принадлежат номера из таблицы телефонов, которые расположены в строках 1 и 2.

Теперь решим обратную задачу – определим, кто имеет доступ к номеру телефона 567575677. Этот номер в таблице телефонов имеет ключ 3. Ищем все записи в связующей таблице, где в поле «Связь с телефоном» равно 3. Это записи с номерами 4 и 5, которые в поле «Связь с именем» содержат значения 2 и 3 соответственно. Если теперь посмотреть на таблицу фамилий, то вы увидите под номерами 2 и 3 Петрова и Сидорова. Значит, именно эти два жителя пользуются телефоном с номером 567575677.

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

CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)
)

CREATE TABLE LinkTable
(
 idLinkTable uniqueidentifier DEFAULT NEWID(),
 idName uniqueidentifier, 
 idPhone uniqueidentifier, 

 CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),

 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Phones (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

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

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

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

На данном этапе мы ограничиваемся только созданием таблиц, а в разделе 2.8 мы вернемся к этой теме и научимся и научимся работать со связанными таблицами. Работа со связью один к одному и один ко многим отличается не сильно, потому что в этой схеме участвует только две таблицы. Связь многие ко многим немного сложнее из-за связующей таблицы, поэтому мы ее рассмотрим отдельно в разделе 2.27.

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

Различные
объекты предметной области, информация
о которых хранится в БД, всегда
взаимосвязаны друг с другом. Такие
взаимосвязи данных отражаются с помощью
внешних
ключей.

Для
таблиц EMPLOYEE,
DEPARTMENT,
PROJECT
существуют три типа взаимосвязей:

1. Один
   к одному (один сотрудник может работать
   в одном отделе).

2. Один
   ко многим (в одном отделе может работать
   несколько сотрудников).

3. Многие
   ко многим (в нескольких отделах может
   выполняться несколько проектов). Это
   связь между таблицами, метаотношение.

Родительское
(или базовое) отношение — это отношение,
которое входит в связь со стороны «один».

Отношение,
входящее в связь со стороны «много»
называется дочерним.

Механизм
реализации связи «один ко многим»
состоит в том, что в дочернее отношение
добавляются атрибуты, являющиеся
ссылками на ключевые атрибуты в
родительском отношении (это внешние
ключи).

Внешний ключ— это подмножество атрибутовFK(foreignkey)
некоторого отношенияR,
которое обладает следующими свойствами:

• Существует отношение Sс потенциальным ключомK

• Каждое значение FKв отношенииRвсегда
  совпадает со значением К для некоторого
  кортежа изS, либо являетсяNULL-значением.

Отношение Rназывается дочерним.

Свойства
внешнего ключа:

1. внешний ключ, так же как и
   потенциальный, может быть простым и
   сложным.

2. внешний ключ должен быть
   определен на тех же доменах, что и
   соответствующий первичный ключ
   родительского отношения.

3. внешний ключ может быть не
   уникальным, так как в дочернем отношении
   может быть несколько кортежей, ссылающихся
   на один и тот же кортеж родительского
   отношения. Это дает отношение «один ко
   многим».

4. если внешний ключ все таки
   обладает свойством уникальности, то
   связь между отношениями имеет тип «один
   к одному» и такие отношения можно
   объединить в одно.

5. хотя каждое значение
   внешнего ключа должно совпадать со
   значением потенциального ключа в
   некотором кортеже родительского
   отношения, но обратное неверно.

6. для внешнего ключа не
   требуется, чтобы он был компонентом
   некоторого потенциального ключа.

7. NULL-значение
   для атрибутов внешнего ключа допустимо
   только в том случае, когда атрибуты
   внешнего ключа не входят в состав
   никакого потенциального ключа.

Лекция №7
(21.03.02.)

Целостность внешних ключей.

Так как внешние
ключи являются ссылками на кортежи в
другом отношении, то эти ссылки не должны
указывать на несуществующие объекты.

Правило целостности
внешних ключей:

Внешние ключи не
должны быть несогласованными.

Замечание:

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

2. Сравнение,
   в которое входит NULL-значение,
   принимает значение неизвестно. Отсюда
   следует, что атрибуты потенциального
   ключа не могут содержать NULL-значение.

Операции, в результате которых нарушается ссылочная целостность:

Существует три таких
операции: вставка, удаление и обновление
кортежей в отношениях.

Для
родительского
отношения:

1. Вставка
   кортежа в родительское отношение. При
   вставке кортежа в родительское отношение
   возникает новое значение потенциального
   ключа. Так как допустимо существование
   кортежей в родительском отношении, на
   которые нет ссылок из дочернего
   отношения, то вставка кортежей в
   родительское отношение не нарушает
   ссылочную целостность.

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

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

Для
дочернего
отношения:

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

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

3. Удаление.
   При удалении кортежа в дочернем отношении
   ссылочная целостность не нарушается.

Внешний ключ — это… Что такое Внешний ключ?

Вне́шний ключ (англ. foreign key) — понятие теории реляционных баз данных, относящееся к ограничениям целостности базы данных.

Неформально выражаясь, внешний ключ представляет собой подмножество атрибутов некоторой переменной отношения R₂, значения которых должны совпадать со значениями некоторого потенциального ключа некоторой переменной отношения R₁.

Формальное определение. Пусть R₁ и R₂ — две переменные отношения, не обязательно различные. Внешним ключом FK в R₂ является подмножество атрибутов переменной R₂ такое, что выполняются следующие требования:

1. В переменной отношения R₁ имеется потенциальный ключ CK такой, что FK и CK совпадают с точностью до переименования атрибутов (то есть переименованием некоторого подмножества атрибутов FK можно получить такое подмножество атрибутов FK’, что FK’ и CK совпадают как по именами, так и по типам атрибутов).
2. В любой момент времени каждое значение FK в текущем значении R₂ идентично значению CK в некотором кортеже в текущем значении R₁. Иными словами, в каждый момент времени множество всех значений FK в R₂ является (нестрогим) подмножеством значений CK в R₁.

При этом для данного конкретного внешнего ключа FK → CK отношение R₁, содержащее потенциальный ключ, называют главным, целевым, или родительским отношением, а отношение R₂, содержащее внешний ключ, называют подчинённым, или дочерним отношением.

Поддержка внешних ключей также называется соблюдением ссылочной целостности. Реляционные СУБД поддерживают автоматический контроль ссылочной целостности.

Пример

Предположим, что в базе данных имеется две таблицы: City (города) и Street (улицы), которые определяются следующим образом:

CREATE TABLE City
(
  id   INTEGER NOT NULL PRIMARY KEY,
  name CHAR(40)
)
 
CREATE TABLE Street
(
  id      INTEGER NOT NULL PRIMARY KEY,
  name    CHAR(40),
  id_city INTEGER NOT NULL FOREIGN KEY REFERENCES City
)

Содержимое этих таблиц следующее:

CITY

STREET

Таблица STREET имеет поле ID_CITY, которое является внешним ключом и ссылается на таблицу CITY. Значение в этом поле соответствует первичному ключу в таблице CITY для того города, где расположена улица. Так, Невский проспект имеет ID_CITY=2, что соответствует Санкт-Петербургу (ID=2 в таблице CITY).

В таблице STREET находятся две улицы с одинаковым названием Пушкинская, которые отличаются значением поля ID_CITY. Одна из них находится в Санкт-Петербурге (ID_CITY=2), другая — во Владивостоке (ID_CITY=3).

Попытка внести в таблицу STREET улицу «Дерибасовская» с ID_CITY=4 вызовет ошибку нарушения ссылочной целостности, поскольку в таблице CITY нет города с ID=4. Однако после внесения в таблицу CITY города «Одесса» с ID=4, повторное внесение улицы «Дерибасовская» с ID_CITY=4 пройдёт успешно.

При удалении из таблицы CITY города Владивостока результат зависит от свойств внешнего ключа:

• Если для внешнего ключа разрешено удаление по цепочке, то вместе с удалением Владивостока будут удалены улицы Светланская и Пушкинская с ID=3.
• Если для внешнего ключа запрещено удаление по цепочке, то операция вызовет ошибку нарушения ссылочной целостности, так как в таблице STREET будут находиться улицы с кодом ID_CITY=3, который отсутствует в таблице CITY.

При изменении в таблице CITY кода города Санкт-Петербурга с 2 на 48 результат зависит от свойств внешнего ключа:

• Если для внешнего ключа разрешено изменение по цепочке, то вместе с изменением кода Санкт-Петербурга будут изменены значения ID_CITY для соответствующих улиц.
• Если для внешнего ключа запрещено изменение по цепочке, то операция вызовет ошибку нарушения ссылочной целостности, так как в таблице STREET будут находиться улицы с кодом ID_CITY=2, который отсутствует в таблице CITY.

Литература

См. также

Справочная целостность БД и внешний ключ

От автора: данный урок, посвящен такой фундаментальной теме, как справочной целостности бд (база данных) MySQL. Урок относится к новому для нашего сайта разделу, посвященному БД, в частности БД MySQL. Данный урок является в некоторой степени презентационым, и представляет он видеокурс, планирующийся к выходу в ближайшее время.

Итак, приступим к изучению целостности БД

Детали урока «Целостность бд, внешний ключ»

Тема: MySQL

Сложность: Средняя

Урок: Видео версия (.mp4)

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Время: 01:02:07

Размер архива: 62 Mb

Прежде всего, давайте определимся с самим понятием целостность БД. С ним, я думаю, Вы уже сталкивались осознанно или неосознанно. В самом общем виде понятие целостности БД подразумевает соответствие имеющейся в ней информации внутренней структуре БД, ее логике.

Например, у нас есть таблица сотрудников, где указаны их имена и табельные номера. Логично, что для поля табельных номеров мы установим числовой тип. Это будет означать, что ничего кроме числового типа в это поле попасть не может. Еще пример — для поля с именами сотрудников, кроме задания его символьного типа, мы также задаем его длину. Это будет означать, что строки больше заданной длины будут обрезаться сервером и в таблицу попадет строка длиной не больше определенного максимума.

Всё это правила, называющиеся правилами ограничения целостности, которые помогают поддерживать указанное выше соответствие.

Теперь о справочной целостности БД. Для того, чтобы понять указанный принцип, создадим БД городов мира. В этой БД мы будем хранить название города и название страны, в которой находится город. Легко понять, что если мы создадим 1 таблицу, то названия стран будут дублироваться, и при этом не раз. Если у нас есть 100 городов одной страны, то мы должны возле каждого города указать одно и то же название страны.

Такая структура порождает т.н. избыточность, которая в свою очередь может повлечь за собой нарушение целостности данных. Чтобы избежать этой ситуации существует такое понятие, как нормализация БД, включающая в себя т.н. нормальные формы. Как раз Вторая нормальная форма относительно придуманной нами таблицы, говорит о необходимости создания дополнительной таблицы. В новой таблице будут храниться названия стран с их идентификаторами, и эти идентификаторы стран будут использоваться в изначальной таблице возле соответствующего города.

Таким образом, у нас появилось 2 таблицы — таблица стран и городов. При этом таблица стран — это т.н. справочник. Почему? Очень легко понять на примере. Итак, имеем таблицу стран с двумя странами: Украина и Россия. Идентификатор Украины будет 1, а России — 2. В таблице стран имеем 3 города: Киев, Москва, Львов. Для принадлежности каждого города к конкретной стране мы для них поставим идентификатор страны — у Киева и Львова — это 1, а у Москвы — 2. А расшифровка этих идентификаторов находится в таблице стран, т.е. в справочнике.

Теперь, если у страны изменится название, то мы меняем его только в справочнике, а таблицу городов не трогаем вообще, поскольку в ней нет названий стан (они в справочнике). Согласитесь, удобно. Фактически мы получили ситуацию, когда идентификатор страны из справочника (родительская таблица) используется в другой таблице (дочерней). Это как раз и есть упоминавшийся выше внешний ключ, который связывает поле дочерней таблицы с полем родителя.

Но пока что эта связь только у нас в уме. Для сервера ее нет. Соответственно, никаких ограничений такая связь не накладывает. Что нам мешает сейчас добавить в таблицу городов город с идентификатором страны 3? Т.е. это страна, не представленная в справочнике. Абсолютно ничего не мешает. Добавив указанную запись в таблицу городов мы как раз нарушаем целостность данных. К примеру, мы выводим на сайт список стран и по клику на страну выводим список городов этой страны. Наш «блудный» город без страны, таким образом, никогда выведен не будет. Получается, что это «лишняя», «болтающаяся» запись.

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

1. Для использования ограничений внешнего ключа типы обоих таблиц должны быть InnoDB.

2. Типы родительского и дочернего полей должны быть абсолютно идентичными.

3. Связь по внешнему ключу опирается на индексы, поэтому и родительское, и дочернее поля должны содержать индексы.

4. Связь по внешнему ключу должна опираться на поле с ограничениями в родительской таблице, т.е. это поле должно содержать ограничения PRIMARY KEY или UNIQUE.

Вот и все. Теперь можем создать 2 таблицы с использованием ограничения по внешнему ключу.

Создаем таблицу городов:

— 1
CREATE TABLE country
(
country_id TINYINT UNSIGNED AUTO_INCREMENT NOT NULL,
country_name VARCHAR(50) NOT NULL,
PRIMARY KEY (country_id)
) ENGINE=InnoDB;

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Создаем таблицу (справочник) стран:

— 2
CREATE TABLE city
(
city_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
city_name VARCHAR(50) NOT NULL,
country_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (city_id),
INDEX ixCity (country_id),
CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
) ENGINE=InnoDB;

Теперь давайте разберем указанные правила с учетом запросов. Поле с внешним ключом — это «city.country_id» (для того, чтобы не запутаться, перед названием поля мы указали через точку имя таблицы). Родительское поле (на которое ссылается внешний ключ) — это «country.country_id»

1. Для использования ограничений внешнего ключа типы обоих таблиц должны быть InnoDB.
Как видим, для обеих таблиц указан одинаковый тип — «ENGINE=InnoDB».

2. Типы родительского и дочернего полей должны быть абсолютно идентичными.
Это правило также выполняется. Тип дочернего поля «city.country_id» — «TINYINT UNSIGNED». Тип родителя «country.country_id» — также «TINYINT UNSIGNED».

3. Связь по внешнему ключу опирается на индексы, поэтому и родительское, и дочернее поля должны содержать индексы. Здесь также все ок. Дочернее поле мы индексируем, добавляя индекс в запросе — «INDEX ixCity (country_id)». Родитель индексируется автоматически. Дело в том, что для родителя мы имеем PRIMARY KEY, что как раз и предусматривает автоматический индекс для него.

4. Связь по внешнему ключу должна опираться на поле с ограничениями в родительской таблице, т.е. это поле должно содержать ограничения PRIMARY KEY или UNIQUE. И здесь все выполняется: «country.country_id» — «PRIMARY KEY (country_id)».

Отлично! Таблицы созданы и связь установлена. Теперь мы не сможем добавить город с идентификатором страны, которая не представлена в справочнике. Кстати, существует и обратная зависимость. По умолчанию мы не сможем также удалить и страну из справочника, если для нее в дочерней таблице есть города, что, в принципе, логично.

Но мы можем улучшить эту связь и сделать так, чтобы если мы что-то меняем в справочнике, то эти изменения касались также дочерней таблицы. Для этого после объявления ограничения по внешнему ключу мы можем записать выражения «ON DELETE CASCADE ON UPDATE CASCADE». По умолчанию значения этих выражений указаны «ON DELETE RESTRICT ON UPDATE RESTRICT», т.е. запрет. Таким образом, наш запрос станет таким:

— 2
CREATE TABLE city
(
city_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
city_name VARCHAR(50) NOT NULL,
country_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (city_id),
INDEX ixCity (country_id),
CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Теперь, если мы удалим из справочника страну, то из дочерней таблицы без всяких дополнительных запросов будут удалены все города этой страны. Удобно, не правда ли? И не нарушается целостность данных.
Ну и напоследок давайте проследим, как можно организовать связь по внешнему ключу через популярное веб-приложение phpMyAdmin.

Итак, прежде всего, должны совпадать типы таблиц:

Типы полей должны также совпадать:

Создаем индекс на поле с внешним ключом, кликая по соответствующей иконке:

Для определения связи в дочерней таблице переходим по ссылке «Relation view»:

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

Вот и все. Таким вот нехитрым способом мы добились сохранения справочной целостности в БД. Надеюсь, что этот урок был полезен для Вас.

Удачи и до новых встреч!

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Хотите изучить MySQL?

Посмотрите курс по базе данных MySQL!

Смотреть

Создание составного внешнего ключа в SQL Server 2008

здесь есть несколько хороших ответов, но я хотел бы сделать этот шаг дальше — ради потомства.

внешний ключ должен ссылаться на Primary key (уникальный кластеризованный индекс) или Unique ограниченный столбец в другой таблице. В принципе, необходимым компонентом является Unique ограничения. Я бы добавил, что вы можете иметь nullable столбцы во внешнем ключе, но если вы разрешаете nulls в «составном» ключе, SQL пропускает проверку данных в отношения с внешним ключом. Это важно помнить, поскольку основная причина, по которой большинство из нас используют внешние ключи, — это обеспечение целостности данных в наших базах данных.

в заключение я хотел бы явно объявить все мои ключевые имена. Почему, спросите вы? Если вам нужно использовать «Полнотекстовое индексирование» в будущем для улучшения возможностей поиска, это не заставляет вас ссылаться на все «автоматически сгенерированные» имена ключей. Это не может быть большой проблемой для небольших проектов, которые не требуется преобразование данных или запланированное полнотекстовое обновление индекса, но если вы создаете сценарии этой функции, вы можете сделать свою работу более сложной (например, необходимость поиска фактического имени имени по умолчанию вашего первичного ключа:pk_someTable_1248594832828495904).

вот что я сделал бы при написании SQL, чтобы избежать каких-либо будущих ловушек:

1. не допускайте нулей на составных внешних ключах, если это возможно.
2. ключи имен явно используют согласованное соглашение об именах (например PK_Schema/56_TalbeName_Col1_Col2). Это не только дает вам стандартное имя ключа, но вы можете легко увидеть из индекса, на какие столбцы ссылаются и в каком порядке.

Код:

CREATE TABLE MySchema.PrimaryTable ( 
  Key1 varchar(20) NOT NULL, 
  Key2 date NOT NULL,
  CONSTRAINT PK_MySchema_PrimaryTable_Key1_Key2 PRIMARY KEY (Key1, Key2)
)
GO 

CREATE TABLE MySchema.SecondaryTable ( 
  AutoID int IDENTITY, 
  Key1 varchar(20) NOT NULL, 
  Key2 date NOT NULL,
  CONSTRAINT FK_MySchema_SecondaryTable_Key1_Key2
     FOREIGN KEY (Key1, Key2) REFERENCES PrimaryTable (Key1, Key2)
)
GO 

OptillectTeam в основном мертв с его ответом. Я просто хотел прояснить несколько важных вещей, о которых раньше не упоминалось. Существует хорошая ссылка на прицел MSDN, обсуждающий это и многое другое на внешних ключах:Ограничение Внешнего Ключа.

внешний ключ — это … Что такое внешний ключ?

.

внешний ключ — это … Что такое внешний ключ?

.

внешний ключ — это … Что такое внешний ключ?

.

внешний ключ — это … Что такое внешний ключ?

.