внешним ключом является поле

Первичный ключ и внешний ключ таблиц реляционных баз данных

Вступление

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

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

Типы данных в базах

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

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

Что такое первичный ключ и внешний ключ таблиц реляционных баз данных

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

Выше мы вспоминали: каждая строка (запись) БД должна быть уникальна. Именно первичный ключ в виде наборов определенных значений, максимально идентифицируют каждую запись. Можно определить по-другому. Первичный ключ: набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ, как primary key.

Primary key (PK) очень важен для каждой таблицы. Поясню почему.

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

Ключ внешний

Foreign key, кратко FK. Обеспечивает однозначную логическую связь, между таблицами одной БД.

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

Более сложный пример.

Две таблицы данных: Люди и Номера телефонов.

Таблица: Люди

Таблица: Номера телефонов

В таблице Номера телефонов PK уникален. FK этой таблицы является PK таблицы Люди. Связь между номерами телефонов и людьми обеспечивает FK таблицы телефонов. То есть:

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

Источник

Внешним ключом является поле

Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (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.

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

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

Источник

Что такое внешний ключ базы данных? Учебник для новичков

Эта статья написана Бриттни Паркер, писателем из Girls Write Tech, которая специализируется на написании технических материалов. Они стремятся побудить больше женщин-разработчиков делиться своими знаниями.

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

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

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

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

Что такое внешний ключ в базе данных?

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

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

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

Столбец, действующий как внешний ключ, должен иметь соответствующее значение в связанной таблице. Это создает ссылочную целостность.

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

Скажем, у нас есть две таблицы с именами customerи order. Мы можем использовать внешний ключ для создания связи между ними. В ordersтаблице мы создаем ключ, который ссылается на клиента (т.е. CUSTOMER_ID) в другой таблице.

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

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

Если мы попытаемся ввести CUSTOMER_IDданные, которых нет в таблице клиентов, мы нарушим целостность ссылочных полномочий таблицы.

Ограничение FK

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

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

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

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

Внешний ключ против первичного ключа

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

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

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

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

Внешний ключ против составного ключа

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

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

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

Ссылочные действия внешнего ключа

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

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

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

Совет: передовой опыт указывает на использование NOT NULLограничения при создании внешних ключей для поддержания структурной целостности базы данных.

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

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

Внешние ключи в SQL и MySQL

Давайте посмотрим на синтаксис с использованием SQL и MySQL. В следующем примере создается FOREIGN KEY в столбце «PersonID».

Узнайте больше о различных типах баз данных здесь

Следующий синтаксис позволяет нам назвать ограничение FOREIGN KEY:

Реальный пример SQL

А теперь давайте уточним. Ниже Actorsтаблица является таблицей, на которую ссылаются, и называется родительской таблицей. Здесь справочная таблица DigitalAssetsявляется дочерней таблицей.

Мы объявляем столбец как внешний ключ в дочерней таблице, только если для столбца определен индекс. Если столбец не имеет индекса, его нельзя использовать в качестве внешнего ключа.

В нашем примере мы изменяем наш DigitalAssetsи устанавливаем ActorIDстолбец как внешний ключ следующим образом:

Теперь, если мы добавим в DigitalAssetsтаблицу строку с идентификатором актера, которого нет в Actorsтаблице, появится сообщение об ошибке:

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

Идентификатор менеджера будет ссылаться на идентификатор сотрудника в том же столбце, а идентификатор сотрудника будет действовать как внешний ключ.

Источник

Взаимные блокировки и внешние ключи в 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 в контейнерах.

Источник

Руководство по проектированию реляционных баз данных (4-6 часть из 15) [перевод]

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

4. ТАБЛИЦЫ И ПЕРВИЧНЫЕ КЛЮЧИ

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

В таблице имеются 6 уроков. Все 6 – разные, но для каждого урока значения одинаковых полей хранятся в таблице, а именно: tutorial_id (идентификатор урока), title (заголовок)и category (категория). Tutorial_id – первичный ключ таблицы уроков. Первичный ключ – это значение, которое уникально для каждой записи в таблице.
В таблице клиентов ниже customer_id – первичный ключ. В данном случае первичный ключ – также уникальное значение (число) для каждой записи.

Первичные ключи в повседневной жизни

В базе данных первичные ключи используются для идентификации. В жизни первичные ключи вокруг нас везде. Каждый раз, когда вы сталкиваетесь с уникальным числом это число может служить первичным ключом в базе данных (может, но не обязательно должно использоваться как таковое. Все базы данных способны автоматически генерировать уникальное значение для каждой записи в виде числа, которое автоматически увеличивается и вставляется вместе с каждой новой записью [Т.н. синтетический или суррогатный первичный ключ – прим.перев.]).

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

Что характеризует первичный ключ? Характеристики первичного ключа.

Первичный ключ служит для идентификации записей.

Первичный ключ используется для идентификации записей в таблице, для того, чтобы каждая запись стала уникальной. Еще одна аналогия… Когда вы звоните в службу технической поддержки, оператор обычно просит вас назвать какой-либо номер (договора, телефона и пр.), по которому вас можно идентифицировать в системе.
Если вы забыли свой номер, то оператор службы технической поддержки попросит предоставить вас какую-либо другую информацию, которая поможет уникальным образом идентифицировать вас. Например, комбинация вашего дня рождения и фамилия. Они тоже могут являться первичным ключом, точнее их комбинация.

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

Первичный ключ всегда имеет уникальное значение. Представьте, что его значение не уникально. Тогда его бы нельзя было использовать для того, чтобы идентифицировать данные в таблице. Это значит, что какое-либо значение первичного ключа может встретиться в столбце, который выбран в качестве первичного ключа, только один раз. РСУБД устроены так, что не позволят вам вставить дубликаты в поле первичного ключа, получите ошибку.
Еще один пример. Представьте, что у вас есть таблица с полями first_name и last_name и есть две записи:

| first_name | last_name |
| vasya |pupkin |
| vasya |pupkin |

Т.е. есть два Васи. Вы хотите выбрать из таблицы какого-то конкретного Васю. Как это сделать? Записи ничем друг от друга не отличаются. Вот здесь и помогает первичный ключ. Добавляем столбец id (классический вариант синтетического первичного ключа) и…

Id | first_name | last_name |
1 | vasya |pupkin |
2 | vasya |pupkin |

Теперь каждый Вася уникален.

Типы первичных ключей.

Обычно первичный ключ – числовое значение. Но он также может быть и любым другим типом данных. Не является обычной практикой использование строки в качестве первичного ключа (строка – фрагмент текста), но теоретически и практически это возможно.
Составные первичные ключи.
Часто первичный ключ состоит из одного поля, но он может быть и комбинацией нескольких столбцов, например, двух (трех, четырех…). Но вы помните, что первичный ключ всегда уникален, а значит нужно, чтобы комбинация n-го количества полей, в данном случае 2-х, была уникальна. Подробнее об этом расскажу позднее.

Поле первичного ключа часто, но не всегда, обрабатывается самой базой данных. Вы можете, условно говоря, сказать базе данных, чтобы она сама автоматически присваивала уникальное числовое значение каждой записи при ее создании. База данных, обычно, начинает нумерацию с 1 и увеличивает это число для каждой записи на одну единицу. Такой первичный ключ называется автоинкрементным или автонумерованным. Использование автоинкрементных ключей – хороший способ для задания уникальных первичных ключей. Классическое название такого ключа – суррогатный первичный ключ [Как и упоминалось выше. – прим. перев.]. Такой ключ не содержит полезной информации, относящейся к сущности (объекту), информация о которой хранится в таблице, поэтому он и называется суррогатным.

5. СВЯЗЫВАНИЕ ТАБЛИЦ С ПОМОЩЬЮ ВНЕШНИХ КЛЮЧЕЙ

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

Один-ко-многим.

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

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

Какую информацию мы будем хранить? Решаем первый вопрос.

Для начала мы определимся какую информацию о заказах и о клиентах мы будем хранить. Чтобы это сделать мы должны задать себе вопрос: “Какие единичные блоки информации относятся к клиентам, а какие единичные блоки информации относятся к заказам?”

Проектируем таблицу клиентов.

Заказы действительно принадлежат клиентам, но заказ – это это не минимальный блок информации, который относится к клиентам (т.е. этот блок можно разбить на более мелкие: дата заказа, адрес доставки заказа и пр., к примеру).
Поля ниже – это минимальные блоки информации, которые относятся к клиентам:

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

Создание таблицы в SQLyog. Обратите внимание, что выбран флажок первичного ключа (PK) для поля customer_id. Поле customer_id является первичным ключом. Также выбран флажок Auto Incr, что означает, что база данных будет автоматически подставлять уникальное числовое значение, которое, начиная с нуля, будет каждый раз увеличиваться на одну единицу.

Проектируем таблицу заказов.
Какие минимальные блоки информации, необходимые нам, относятся к заказу?

Ниже – пример таблицы в SQLyog.

Проект таблицы. Поле customer является ссылкой (внешним ключом) для поля customer_id в таблице клиентов.

Эти две таблицы (клиентов и заказов) связаны потому, что поле customer в таблице заказов ссылается на первичный ключ (customer_id) таблицы клиентов. Такая связь называется связью по внешнему ключу. Вы должны представлять себе внешний ключ как простую копию (копию значения) первичного ключа другой таблицы. В нашем случае значение поля customer_id из таблицы клиентов копируется в таблицу заказов при вставке каждой записи. Таким образом, у нас каждый заказ привязан к клиенту. И заказов у каждого клиента может быть много, как и говорилось выше.

Создание связи по внешнему ключу.

Вы можете задаться вопросом: “Каким образом я могу убедиться или как я могу увидеть, что поле customer в таблице заказов ссылается на поле customer_id в таблице клиентов”. Ответ прост – вы не можете сделать этого потому, что я еще не показал вам как создать связь.
Ниже – окно SQLyog с окном, которое я использовал для создания связи между таблицами.

Создание связи по внешнему ключу между таблицами заказов и клиентов.

В окне выше вы можете видеть, как поле customer таблицы заказов слева связывается с первичным ключом (customer_id) таблицы клиентов справа.

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

Заказы связаны с клиентами через поле customer, которое ссылается на таблицу клиентов.

На изображении вы видите, что клиент mary поместила три заказа, клиент pablo поместил один, а клиент john – ни одного.
Вы можете спросить: “А что же именно заказали все эти люди?” Это хороший вопрос. Вы возможно ожидали увидеть заказанные товары в таблице заказов. Но это плохой пример проектирования. Как бы вы поместили множественные продукты в единственную запись? Товары – это отдельные сущности, которые должны храниться в отдельной таблице. И связь между таблицами заказов и товаров будет являться связью один-ко-многим. Я расскажу об этом далее.

6. СОЗДАНИЕ ДИАГРАММЫ СУЩНОСТЬ-СВЯЗЬ

Ранее вы узнали как записи из разных таблиц связываются друг с другом в реляционных базах данных. Перед созданием и связыванием таблиц важно, чтобы вы подумали о сущностях, которые существуют в вашей системе (для которой вы создаете базу данных) и решили каким образом эти сущности бы связывались друг с другом. В проектировании баз данных сущности и их отношения обычно предоставляются в диаграмме сущность-связь (англ. entity-relationship diagram, ERD). Данная диаграмма является результатом процесса проектирования базы данных.

Сущности.

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

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

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

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

Если вы не уверены, то просто подумайте о том, какую информацию о платежах вы хотите хранить. Возможно, вы захотите хранить метод платежа или дату платежа. Но это все еще минимальные блоки информации, которые могли бы относиться к заказу. Можно изменить формулировки. Метод платежа — метод платежа заказа. Дата платежа – дата платежа заказа. Таким образом, я не вижу необходимости выносить платежи в отдельную таблицу, хотя концептуально вы бы могли выделить платежи как сущность, т.к. вы могли бы рассматривать платежи как контейнер информации (метод платежа, дата платежа).

Давайте не будет слишком академичными.

Как вы видите, есть разница между сущностью и непосредственно таблицей в базе данных, т.е. это не одно и то же. Специалисты отрасли информационных технологий могут быть ОЧЕНЬ академичными и педантичными в этом вопросе. Я не такой специалист. Эта разница зависит от вашей точки зрения на ваши данные, вашу информацию. Если вы смотрите на моделирование данных с точки зрения программного обеспечения, то вы можете прийти к множеству сущностей, которые нельзя будет перенести напрямую в базу данных. В данном руководстве мы смотрим на данные строго с точки зрения баз данных и в нашем маленьком мире сущность – это таблица.

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

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

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

Связи.

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

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

Источник