Что такое первичный и внешний ключ в sql
Что такое первичный и внешний ключ в sql
Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (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 (название статьи)
В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.
Связь многие ко многим
Но в SQL Server на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность.
Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:
TagId (идентификатор, первичный ключ)
Также пусть будет промежуточная таблица 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.
Аномалии обновления
Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.
Первичные и внешние ключи MySQL
Первичный ключ может быть любое поле или столбец таблицы, который должен быть уникальным и не нулевое значение для каждой записи или строке.
Внешний ключ представляет собой поле, которое содержит первичный ключ некоторой другой таблицы, чтобы установить связь между друг другом.
Давайте посмотрим на синтаксис и различные примеры для создания первичных и внешних ключей в MySQL.
Основные ключи
Мы можем создать первичный ключ для сегмента таблицы, используя ALTER TABLE.
Первичный ключ при создании таблицы
Предположим, мы хотим создать таблицу books в MySQL, которая содержит идентификатор, имя и категорию книги, в которой столбец идентификатора будет первичным ключом.
Запрос на создание такой таблицы и создание столбца идентификатора столбца первичного ключа будет таким:
В этом синтаксисе мы можем определить ограничения в конце запроса.
В столбце ключа мы видим, что book_id установлен как первичный ключ таблицы.
Отбросить первичный ключ
Вместо добавления, если мы хотим удалить или отбросить первичный ключ, используется команда ALTER.
Итак, это все о том, как мы можем создавать и удалять первичный ключ в таблице.
Создание первичного ключа с помощью ALTER TABLE
Чтобы определить первичный ключ, мы можем использовать ALTER TABLE.
Первичный ключ успешно добавлен. Теперь давайте лучше узнаем о внешних ключах.
Внешние ключи
Как и первичные ключи, внешние ключи могут быть определены при определении таблицы с помощью команды ALTER TABLE.
Внешний ключ при создании таблицы
В разделе первичного ключа мы создали таблицу для книг. Теперь предположим, что у нас есть еще одна таблица authors в нашей базе данных, которая включает идентификатор автора в качестве первичного ключа, имя и фамилию автора,
И мы хотим создать внешний ключ к идентификатору автора в таблице книг. Итак, чтобы создать внешний ключ для author_id при создании таблицы книг, мы запускаем этот запрос:
Отбросьте внешний ключ
Удаление внешнего ключа – это не то же самое, что удаление первичного ключа. Сначала мы должны получить имя ограничений, выполнив команду «SHOW CREATE TABLE books».
Затем укажите имя ограничения для команды ALTER TABLE следующим образом:
Вот как мы можем создавать и удалять внешний ключ в таблице.
Первичный ключ с помощью команды ALTER TABLE
Для создания внешнего ключа в существующей таблице с помощью команды ALTER TABLE,
Мы видим, что author_id успешно установлен в качестве внешнего ключа.
Резюме
Мы узнали о сути и концепциях первичных и внешних ключей. А также создание, добавление и удаление первичного или внешнего ключа в таблице.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Интернет и базы данных. Часть 04. Ключи и ссылочная целостность
Первичный ключ
Уникальный ключ
Внешний ключ
И самое главное. Все значения внешнего ключа должны совпадать с каким-либо из значений родительского ключа. (Заметим в скобках насчет совпадения / несовпадения: нюансы возникают, когда в значениях столбцов вторичного ключа встречается NULL. Давайте пока в эти нюансы вдаваться не будем). Появление значений внешнего ключа, для которых нет соответствующих значений родительского ключа, недопустимо. Вот тут-то мы плавно переходим к понятию ссылочной целостности.
Ссылочная целостность
Первое из правил ссылочной целостности фактически уже изложено в предыдущем абзаце: в таблице не допускается появления (неважно, при добавлении или при модификации) строк, внешний ключ которых не совпадает с каким-либо из имеющихся значений родительского ключа.
Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \»болтающихся в воздухе\» строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:
Ключи и ссылочная целостность в MySQL и Oracle
Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:
Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.
MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.
BestProg
Реляционные базы данных. Понятие ключа. Виды ключей. Отношения. Главная и подчиненная таблицы
В данной теме, на примере двух таблиц, определяются основные понятия реляционных баз данных, а именно:
Содержание
Поиск на других ресурсах:
Входные данные
Пусть задана база данных работников предприятия, которая состоит из двух таблиц. Первая таблица содержит данные о работнике. Вторая таблица содержит сведения о заработной плате работника.
Таблицы имеют следующую структуру.
Таблица «Работник». Содержит данные о работнике
Таблица «Зарплата». Содержит сведения о заработной плате работников.
Вопрос/ответ
1. Что такое первичный ключ в таблице базы данных? Для чего используются первичные ключи?
При работе с таблицами в реляционных базах данных, желательно (необходимо), чтобы каждая таблица имела так называемый первичный ключ.
Первичный ключ – это поле, которое используется для обеспечения уникальности данных в таблице. Это означает, что значение (информация) в поле первичного ключа в каждой строке (записи) таблицы может быть уникальным.
Уникальность необходима во избежание неоднозначности, когда неизвестно к какой записи таблицы можно обратиться, если в таблице есть повторяющиеся записи (две записи имеют одинаковые значения во всех полях таблицы).
Пример. Для таблицы «Работник» можно ввести дополнительное поле, которое будет первичным ключом. Однако, поле (атрибут) «Табельный номер» также обеспечивает уникальность. Так как, теоретически, не может быть двух одинаковых табельных номеров. На практике могут быть случаи, что один и тот же табельный номер будет введен по ошибке и совпадут значения всех полей таблицы. В результате возникнут два одинаковых записи в таблице. Во избежание такой ошибки, лучше создать в таблице дополнительное поле-счетчик, которое обеспечит уникальность.
Также для таблицы «Зарплата» можно ввести дополнительное поле, которое будет первичным ключом.
2. Что такое отношение (связь) между таблицами (relationship)? Пример
Таблицы в реляционной модели данных могут иметь связи между собой. Такие связи называются отношениями. Для таблиц «Работник» и «Зарплата» можно установить связь по полю «Табельный номер».
Пример. Проанализируем таблицы «Работник» и «Зарплата». В этих таблицах можно установить отношение между таблицами на основе поля «Табельный номер». То есть, связь между таблицами происходит на основе поля (атрибуту) «Табельный номер».
Это означает следующее. Если нужно найти начисленную заработную плату в таблице «Зарплата» для работника Иванов И.И., то нужно выполнить следующие действия:
Рис. 1. Иллюстрация связи между таблицами. Табельный номер 2145 таблицы «Работник» отображается в таблице «Зарплата»
Рис. 2. Связь (отношение) между полями таблиц
3. Что такое внешний ключ (foreign key)? Пример
Понятие «внешний ключ» есть важным при рассмотрении связанных таблиц.
Внешний ключ – это одно или несколько полей (атрибутов), которые являются первичными в другой таблице и значение которых заменяется значениями первичного ключа другой таблицы.
Пример. Пусть между таблицами «Работник» и «Зарплата» существует взаимосвязь по полю «Табельный номер». В этом случае, поле «Табельный номер» таблицы «Работник» может быть первичным ключом, а поле «Табельный номер» таблицы «Зарплата» внешним ключом. Это означает, что значения поля «Табельный номер» таблицы «Зарплата» заменяются значениями поля «Табельный номер» таблицы «Работник».
4. Что такое рекурсивный внешний ключ?
Рекурсивный внешний ключ – это внешний ключ, который ссылается на одну и ту же таблицу, к которой он принадлежит. В этом случае поле (атрибут), которое соответствует внешнему ключу, есть ключом одного и того же отношения (связи).
5. Могут ли первичный и внешний ключи быть простыми или составными (сложными)?
Первичный, вторичный и внешний ключи могут быть как простыми так и составными (сложными). Простые ключи – это ключи, которые содержат только одно поле (один атрибут). Составные (сложные) ключи – это ключи, которые содержат несколько полей (атрибутов).
6. Какое отличие между искусственным и естественным ключом? Пример
Естественной ключ обеспечивает уникальность из самой сущности предметной области. Бывают случаи, когда значения записей некоторого поля (полей) таблицы есть уникальными. Это поле может быть естественным ключом.
Искусственный ключ вводится дополнительно для обеспечения уникальных значений. Чаще всего искусственный ключ есть полем типа счетчик (counter). В таком поле, при добавлении новой записи (строки) в таблицу, значение счетчика увеличивается на 1 (или другую величину). Если запись удалить из таблицы, то максимальное значение счетчика строк уже не уменьшается, а остается как есть. Как правило, за этим все следит система управления базами данных.
Пример. В таблице «Работник» естественном ключом есть поле (атрибут) «Табельный номер». Поле «Табельный номер» есть само по себе уникальным, так как не может быть двух работников с одинаковым табельным номером.
В таблице «Зарплата» значение во всех четырех полях могут случайно повториться. Поэтому, здесь целесообразно добавить дополнительное поле-счетчик, которое будет искусственным ключом. В этом случае таблица «Зарплата» с дополнительным полем может иметь приблизительно следующий вид:
где поле «Номер» есть искусственным ключом, который обеспечивает уникальность.
7. Какие существуют способы выбора первичного ключа?
Существует 3 способа выбора первичного ключа:
8. Что означают термины «главная таблица» (master) и «подчиненная таблица» (detail)?
Если между таблицами есть связь, то одна из них может быть главной (master), а другая подчиненной (detail). Главная таблица отображает все записи, которые помещаются в ней. Подчиненная таблица отображает только те записи, которые соответствуют значению ключа главной таблицы, который на данный момент есть активным (текущим). Если изменяется текущая запись главной таблицы, то изменяется множество доступных записей подчиненной таблицы.
Пример. Если рассмотреть таблицы «Работник» и «Зарплата», то таблица «Работник» есть главной, а таблица «Зарплата» есть подчиненной.
9. Какие существуют типы отношений (связей) между таблицами?
Существует 4 основных типа отношений между таблицами:
Пример. Если рассмотреть отношение между таблицами «Работник» и «Зарплата», то это отношения есть типа «один ко многим». Таблица «Работник» есть главной. Таблица «Зарплата» есть подчиненной.
Взаимные блокировки и внешние ключи в SQL Server
Введение
В реляционных базах данных внешние ключи (foreign key) используются для обеспечения целостности связей между таблицами. Простыми словами, внешний ключ — это столбец (или несколько столбцов), ссылающийся на первичный ключ другой таблицы. Таблица с внешним ключом называется дочерней, а с первичным — родительской. При вставке строки в дочернюю таблицу проверяется наличие значения внешнего ключа в родительской таблице. Эти дополнительные операции иногда могут вызывать проблемы с блокировками и приводить к взаимоблокировкам. В этой статье мы изучим, почему это происходит, и как решать подобные проблемы.
Будем использовать две таблицы: Department (Отдел) и Employee (Сотрудник). Столбец DepId в таблице Employee определен как внешний ключ, поэтому значения этого столбца будут проверяться на наличие соответствующих значений в столбце DepartmentId таблицы Department.
Что происходит за кулисами INSERT
Исследуем, какие операции выполняются при вставке данных в дочернюю таблицу (Employee).
Сначала вставим строку в родительскую таблицу (Department).
Перед выполнением следующего запроса включим отображение фактического плана выполнения и вставим строку в таблицу Employee (дочернюю).
Clustered Index Insert вставляет данные в кластерный индекс, а также обновляет некластерные индексы. Если внимательно посмотреть на этот оператор, то можно заметить, что для него не указано имя объекта. Причина этого как раз в том, что при вставке данных в кластерный индекс таблицы Employee, эти данные одновременно добавляются в некластерный индекс. Эти два индекса можно увидеть во всплывающей подсказке оператора Clustered Index Insert.
Clustered Index Seek проверяет существование значения внешнего ключа в родительской таблице.
Nested Loops сравнивает вставленные значения внешних ключей со значениями, возвращаемые оператором Clustered Index Seek. В результате этого сравнения на выходе получается результат, который указывает, существует значение в родительской таблице или нет.
Assert оценивает результат оператора Nested Loops. Если Nested Loops возвращает NULL, то результат Assert будет ноль, и запрос вернет ошибку. В противном случае операция INSERT выполнится успешно.
Взаимные блокировки
При вставке данных в столбец с внешним ключом выполняются дополнительные операции по проверке существования данных в родительской таблице. В некоторых случаях, например, при массовой вставке, эта проверка может привести к взаимоблокировкам, когда несколько транзакций пытаются обратиться к одним и тем же данным. Попробуем смоделировать эту ситуацию. Сначала вставим данные в таблицу Department.
После этого создадим глобальную временную таблицу, которая поможет со вставкой строк в Employee.
Следующие запросы выполним в разных сессиях. Сначала «Часть 1» первого запроса:
И первую часть второго запроса:
А теперь — вторые части запросов.
В результате возникла взаимная блокировка.
Давайте проанализируем, что произошло:
Первая часть Запроса-1 открывает транзакцию и вставляет строку в таблицу Department. Страница данных Department блокируется монопольной блокировкой намерения (IX, intent exclusive lock), а вставленная строка — монопольной блокировкой (X, exclusive lock).
Первая часть Запроса-2 также открывает транзакцию и вставляет строку в Department. Страница данных таблицы Department блокируется монопольной блокировкой намерения (IX), а вставленная строка — монопольной блокировкой (X). На данный момент проблем с блокировками нет.
Вторая часть Запроса-1, он начинает сканировать первичные ключи таблицы Department для проверки ссылочной целостности вставленных строк. Однако одна из строк заблокирована монопольной блокировкой в Запросе-2. В этом случае Запрос-1 должен дождаться завершения Запроса-2.
Запрос-2 блокируется при попытке прочитать строки, вставленные в Department в Запросе-1. У нас получилась взаимная блокировка.
Приведенный ниже граф взаимных блокировок иллюстрирует то, о чем мы говорили. Сессия 71 (Запрос-1) получил монопольную блокировку (X) для строк таблицы Employee и хочет получить разделяемую блокировку (S) для строк таблицы Department. В это же время сессия 51 получила эксклюзивную блокировку (X) для строк таблицы Department и хочет получить монопольную блокировку (X) для строк таблицы Employee. В результате между этими двумя сессиями возникает борьба за ресурсы, и SQL Server завершает одну из сессий.
Устранение взаимных блокировок
Мы с вами увидели, что при массовых INSERT проверка целостности внешнего ключа вызывает проблему с блокировками. На самом деле эта проблема связана с методом доступа к данным родительской таблицы. Взглянув на план выполнения второй части запросов, мы увидим оператор Merge Join.
Соединение Merge Join является самым эффективным, но требует предварительной сортировки входных данных. В нашем случае при сканировании родительской таблицы Merge Join сталкивается с заблокированной строкой, и не может продолжить сканирование, пока блокировка не будет снята.
Мы можем изменить метод доступа к данным с помощью OPTION (LOOP JOIN). При использовании хинта LOOP JOIN, оптимизатор запросов SQL Server сгенерирует другой план выполнения и заменит оператор Merge Join оператором Nested Loops, а оператор Clustered Index Scan будет заменен оператором Clustered Index Seek. С помощью Clustered Index Seek доступ к данным родительской таблицы осуществляется напрямую, поэтому не требуется ждать заблокированных строк. С другой стороны, оператор Nested Loops выполняет построчное чтение, а Merge Join — одно последовательное чтение. Эти два изменения метода доступа к данным снижают вероятность блокировки запроса из-за наличия других блокировок.
Row Count Spool используется для подсчета количества строк, возвращаемых оператором Clustered Index Seek, и передачи этой информации в оператор Nested Loops. Этот оператор используется оптимизатором запросов SQL Server для проверки существования строк, но не содержащихся в них данных.
Заключение
В этой статье мы узнали, как внешние ключи влияют на план запроса INSERT и добавляют некоторые операции в процесс его выполнения. Также мы увидели, что в некоторых ситуациях внешние ключи могут приводить к взаимным блокировкам. Для устранения проблем с блокировками можно использовать хинт LOOP JOIN.
Материал подготовлен в рамках курса «MS SQL Server Developer». Всех желающих приглашаем на открытый урок «SQL Server и Docker». На открытом уроке мы поговорим о контейнерах, а также рассмотрим развертывание SQL Server в контейнерах.