Как установить связь один к одному?

Как установить связь один к одному?

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

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

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

Создание связи «один к одному»

Проверьте, как это работает!

Что такое связь «один к одному»?

Связи «один к одному» часто используются для получения важных данных, необходимых для ведения бизнеса.

Связь «один-к-одному» — это связь между информацией из двух таблиц, когда каждая запись используется в каждой таблице только один раз. Например, связь типа «один-к-одному» может использоваться между сотрудниками и их служебными автомобилями. Каждый работник указан в таблице «Сотрудники» только один раз, как и каждый автомобиль в таблице «Служебный транспорт».

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

Общие сведения о создании связи «один к одному»

Связи «один-к-одному» создаются путем связывания индекса первой таблицы, в качестве которого обычно выступает первичны ключ, с индексом второй таблицы, причем их значения совпадают. Пример:

Часто бывает, что лучший способ создать подобную связь — назначить вторичной таблице функцию поиска значений из первой таблицы. Например, вы можете сделать поле «Код автомобиля» в таблице «Сотрудники» полем подстановки, которое будет искать значение индекса «Код автомобиля» в таблице «Служебный транспорт». Таким образом исключается случайное добавление кода автомобиля, который на самом деле не существует.

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

Целостность данных помогает Access поддерживать порядок данных путем удаления связанных записей. Например, при удалении сотрудника из таблицы «Сотрудники» также удаляются записи о его льготах из таблицы «Льготы». Но в некоторых связях, таких как в этом примере, целостность данных не имеет смысла: если удалить сотрудника, мы не хотим, чтобы автомобиль удалялся из таблицы «Автомобиль компании», так как он по-прежнему будет принадлежать компании и будет назначен другому сотруднику.

Инструкции по созданию связи типа «один к одному»

Вы можете создать связь «один-к-одному», добавив в таблицу поле подстановки. (Инструкции см. в статье Создание таблиц и назначение типов данных.) Например, чтобы указать, какие автомобили назначены определенным сотрудникам, вы можете добавить в таблицу «Сотрудники» поле «Код автомобиля». После этого воспользуйтесь мастером подстановок для создания связи между полями.

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

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

Выберите таблицу с ключом (обычно первичным), который вы хотите добавить в первую таблицу, и нажмите кнопку Далее. В рассмотренном примере следует выбрать таблицу «Служебный транспорт».

Добавьте в список Выбранные поля поле с необходимым ключом. Нажмите кнопку Далее.

Задайте порядок сортировки и, при необходимости, измените ширину поля.

В последнем окне установите флажок Включить проверку целостности данных и нажмите кнопку Готово.

Связи между таблицами базы данных

1. Введение

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

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

2. Благодарности

Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!

3.1. Как организовываются связи?

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

3.2. Виды связей

Связи делятся на:

1. Многие ко многим.
2. Один ко многим.
   • с обязательной связью;
   • с необязательной связью;
3. Один к одному.
   • с обязательной связью;
   • с необязательной связью;

Рассмотрим подробно каждый из них.

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

Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:

• Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
• Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.

Работников представляет таблица «Employee» (id, имя, возраст), должности представляет таблица «Position» (id и название должности). Как видно, обе эти таблицы связаны между собой по правилу многие ко многим: каждому работнику соответствует одна и больше должностей (многие должности), каждой должности соответствует один и больше работников (многие работники).

4.1. Как построить такие таблицы?

EmployeeId	PositionId
1	1
1	2
2	3
3	3

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

На эту таблицу можно посмотреть с двух сторон:

1. Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
2. Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.

4.2. Реализация

С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы

• ссылаемся атрибутом PositionId таблицы EmployeesPositions на атрибут PositionId таблицы Position;
• атрибутом EmployeeId таблицы EmployeesPositions — на атрибут EmployeeId таблицы Employee;

4.3. Вывод

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

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

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

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

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

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

5.1. Как построить такие таблицы?

PhoneId	PersonId	PhoneNumber
1	5	11 091-10
2	5	19 124-66
3	17	21 972-02

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

5.2. Почему мы не делаем тут таблицу-посредника?

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

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

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

5.3. Реализация

6. Один к одному

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:

DisabledPersonId	EmployeeId
1	159
2	722
3	937

Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.

Выполнив это мы получили связь один к одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

6.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

6.2. Реализация

7. Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

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

У одной биологической матери может быть много детей. У ребенка есть только одна биологическая мать.

А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.

7.2. Один к одному

У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.

А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.

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

Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).

А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.

8. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

1. Возле таблицы Person находится золотой ключик. Он обозначает слово «один».
2. Возле таблицы Phone находится знак бесконечности. Он обозначает слово «многие».

9. Итоги

10. Задачи

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

1. Описать таблицу фильм: id, название, длительность, режиссер, жанр фильма. Обратите внимание на то, что у фильма может быть более одного жанра, а к одному жанру может относится более, чем один фильм.
2. Описать таблицу песня: id, название, длительность, певец. При этом у песни может быть более одного певца, а певец мог записать более одной песни.
3. Реализовать таблицу машина: модель, производитель, цвет, цена
   • Описать отдельную таблицу производитель: id, название, рейтинг.
   • Описать отдельную таблицу цвета: id, название.

   У одной машины может быть только один производитель, а у производителя — много машин. У одной машины может быть много цветов, а у одного цвета может быть много машин.

4. Добавить в БД из пункта 6.2. таблицу военно-обязанных по типу того, как мы описали отдельную таблицу DisabledEmployee.

Отношение один-к-одному (one-to-one) между таблицами

между двумя таблицами базы данных указывает на то, что для одной строки главной таблицы, обязательно определяется одна связанная строка в зависимой таблице. Это отношение имеет еще один подвид, который называется ноль-или-один-к-одному (zero-or-one-to-one). При этом отношении, в зависимой таблице необязательно указывать связанную строку, т.е. одной строке в главной таблице, может соответствовать ноль или одна строка в зависимой таблице.

При определении отношения один-к-одному в модели данных Entity Framework, вы должны использовать только двустороннюю связь между классами, т.е. нужно указывать навигационное свойство в каждой таблице (если вы используете Fluent API для настройки, следовать этому правилу необязательно). Эти навигационные свойства должны иметь тип ссылки (а не коллекции), чтобы Code-First понял, что используется связь один-к-одному, при этом используемой по умолчанию связью, будет ноль-или-один-к-одному.

Давайте добавим в нашу модель класс профиля покупателя Profile, который свяжем с таблицей Customer:

В этом примере изначально допущена ошибка. Code-First не сможет определить, какой класс является зависимым в этой ситуации (т.е. для какого класса нужно создать внешний ключ). Если вы запустите приложение с этой моделью, то EF сгенерирует исключение InvalidOperationException, указав, что не может определить основной класс в связке Profile-Customer.

Простым решением этой проблемы является явное указание внешнего ключа в зависимой таблице. При создании отношения один-к-одному Entity Framework требует, чтобы внешний ключ в зависимой таблице являлся и первичным ключом для этой таблицы. В нашем случае в классе Profile определен первичный ключ CustomerId, для которого мы должны указать, что он является и внешним ключом. Как описывалось ранее, это можно сделать указав свойству CustomerId атрибут ForeignKey, с переданным ему именем навигационного свойства:

Если вы теперь запустите приложение, то Entity Framework корректно создаст таблицу Profiles. Как показано на рисунке ниже, ключ CustomerId является как первичным, так и внешним ключом для этой таблицы:

Ранее, при обсуждении использования внешних ключей мы говорили, что атрибут ForeignKey можно применить не только к свойству ключа в модели, но и к навигационному свойству. Для нашей модели такой подход является ошибочным, если вы примените атрибут [ForeignKey(“CustomerId”)] к свойству CustomerOf в классе Profile, Code-First не поймет, в какой таблице нужно будет создать внешний ключ, т.к. и таблица Customer и Profile содержат поле CustomerId. Так что данный способ определения внешних ключей не подходит для нашего сценария.

На следующем рисунке показана диаграмма, на которой можно видеть отношение ноль-или-один-к-одному между таблицами Customers и Profiles:

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

Обратите внимание, что здесь не нужно использовать метод HasForeignKey() для явного указания внешнего ключа. Этого кода на самом деле достаточно для Code-First, чтобы показать, что класс Profile является зависимым, а свойство Profile.CustomerId должно являться внешним ключом. Это связано с тем, что при определении отношения one-to-one (или zero-or-one-to-one) первичный ключ в зависимой таблице должен автоматически быть внешним ключом.

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

В результате, если мы попытаемся вставить данные нового заказчика, не указав для него профиль, то Entity Framework сгенерирует ошибку. Следует отметить, что при реализации отношения один-к-одному структура базы данных не меняется, изменения касаются только модели, которые уведомляют EF об обоюдной зависимости таблиц Customer и Profile.

Отношение один-к-одному можно создать, используя также Fluent API. Логично было бы предположить, что для этого используется вызов метода HasRequired с последующим вызовом WithRequired. Однако, Fluent API предлагает вместо вызова WithRequired использовать два следующих метода: WithRequiredDependent или WithRequiredPrincipal. Выбор нужного метода зависит от того, к какой таблице он применяется – зависимой (WithRequiredPrincipal укажет ссылку на основную таблицу) или основной (WithRequiredDependent укажет ссылку на зависимую таблицу). Чтобы в этом разобраться, достаточно взглянуть на простой пример:

Здесь важно отметить, что если вы перепутаете в этом примере вызовы этих методов, например:

Тогда Entity Framework создаст таблицу Customer как зависимую от Profile. Это выражается в том, что внешний ключ будет установлен в поле Customer.CustomerId, а не в Profile.CustomerId, как это делалось раньше.

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

Существует еще одна разновидность отношения один-к-одному, которую мы еще не рассмотрели, это отношение ноль-или-один-к-ноль-или-одному (zero-or-one-to-zero-or-one). В контексте нашего примера это отношение говорит о том, что для любого покупателя может существовать ноль или один профиль, и для любого профиля может существовать ноль или один покупатель. В модели магазина такая связь не имеет логического смысла – как может существовать профиль пользователя без пользователя? Однако, в некоторых случаях вам может понадобиться создать эту связь.

Для реализации связи ноль-или-один-к-ноль-или-одному в Fluent API существуют специальные методы WithOptionalDependent() и WithOptionalPrincipal(), смысл которых аналогичен соответствующим методам WithRequired… , но они применяются после вызова метода HasOptional(), как показано в примере ниже:

Обратите внимание, что в этом примере мы явно задаем имя для внешних ключей, используя вспомогательный метод Map(). В результате, Entity Framework добавит внешний ключ как в таблицу Customers, так и в таблицу Profiles:

Фактически, отношение ноль-или-один-к-ноль-или-одному идентично двум отношениям ноль-или-один-к-одному между нашими таблицами.

jtest.ru

HTML, CSS, JavaScript, JQuery, PHP, MySQL

• О сайте
• Новости
• HTML / CSS
• JavaScript / jQuery
• PHP
• Базы данных

SQL для начинающих. Часть 3

Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 3

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

Предыдущие статьи

• SQL для начинающих. Часть 1
• SQL для начинающих. Часть 2

Вступление

При проектировании базы данных, здравый смысл подсказывает нам, что мы должны использовать различные таблицы для разных данных. Пример: клиенты, заказы, записи, сообщения и т.д. Так же мы должны иметь взаимосвязи между этими таблицами. Например, клиент имеет заказы, а у заказа есть позиции (товары). Эти взаимосвязи должны быть отражены в базе данных. А также, когда мы получаем данные с помощью SQL, мы должны использовать определенные типы запросов JOIN, чтобы получить нужный результат.

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

• Отношения один к одному
• Один ко многим и многие к одному
• Многие ко многим
• Связь с самим собой

Когда данные выбираются из нескольких связанных таблиц, мы будем использовать запрос JOIN. Есть несколько типов присоединения, мы познакомимся с этими:

• Cross Joins (Перекрестное соединение)
• Natural Joins (Естественное соединений)
• Inner Joins (Внутреннее соединений)
• Left (Outer) Joins (Левое (внешнее) соединение)
• Right (Outer) Joins (Правое (внешнее) соединение)

Также мы изучим предложения ON и USING.

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

Допустим есть таблица покупателей (customers):

Мы можем расположить информацию о адресе покупателя в другой таблице:

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

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

Вот так можно отобразить отношения между покупателями и адресами:

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

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

Этот тип отношений наиболее часто встречающийся. Рассмотрим такой сайт интернет магазина:

• У покупателей может быть несколько заказов.
• Заказ может содержать несколько товаров.
• Товары могут иметь описание на нескольких языках.

В этих случаях нам потребуется создать связь «Один ко многим». Пример:

Каждый покупатель может иметь 0 или более заказов. Но каждый заказ может принадлежать только одному покупателю.

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

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

Для такой связи нам потребуется создать дополнительную таблицу:

Назначение таблицы «Items_Orders» только одно — создать связь «Многие ко многим» между товарами и заказами.

Так можно представить этот тип отношений:

Если добавить записи items_orders к диаграмме, то она будет выглядеть так:

Связь с собой

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

Покупатели 102 и 103 ссылаются на покупателя 101.

Этот тип похож на связь «Один ко многим», поскольку один покупатель может ссылаться на несколько покупателей. Это можно представить как древовидную структуру:

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

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

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

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

В отношениях, обсуждаемых выше, у нас всегда было поле вида «****_id», которое ссылалось столбец в другой таблице. В нашем примере столбец customer_id, в таблице Orders, является внешним ключом:

В таких базах как MySQL есть два способа создания внешних ключей:

Задать внешний ключ явно

Создадим простую таблицу с покупателями:

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

Оба столбца (customers.customer_id и orders.customer_id) должны быть одного типа. Если у первого тип INT, то второй не должен быть типа BIGINT, например.

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

Без явного объявления

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

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

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

Отображение связей

В данный момент, моей любимой программой для проектирования баз данных и отображения связей является MySQL Workbench.

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

Запросы JOIN

Чтобы получить связанные данные из базы данных следует использовать запросы JOIN.

Прежде чем мы начнем, давайте создадим для работы тестовые таблицы и данные.

У нас есть 4 покупателя. У одного из них два заказа, у двоих по одному заказу, и у одного вообще нет заказов. Теперь давайте посмотрим какие виды запросов JOIN мы можем выполнять с этими таблицами.

Cross Join (Перекрестное объединение)

Это вид JOIN запроса по-умолчанию, если не определено условие.

Результатом будет, так называемое, «Декартово объединение» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Т.к. в каждой таблице по 4 строки, мы получили в результате 16 строк.

Ключевое слово JOIN можно заменить на запятую, в этом случае.

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

Natural Join (Естественное объединение)

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

Как Вы можете видеть, в этот раз столбец customer_id отображаются только один раз, потому что движок базы рассматривает этот столбец как общий. Мы видим два заказа Adam’а, и другие два заказа Joe и Sandy. Наконец мы получили некоторую полезную информацию.

Inner Join (Внутреннее объединение)

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

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

Добавим побольше условий к запросу.

На этот раз возвращается только те заказы, сумма которых превышает $15.

Предложение ON

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

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

Предложение USING

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

На самом деле это очень похоже на NATURAL JOIN, т.е. объединяющий столбец (customer_id) не повторяется дважды.

Left (Outer) Join (Левое внешнее соединение)

LEFT JOIN это вид внешнего соединения. В следующем запросе, если не найдены совпадения во второй таблице, записи из первой таблице все равно отобразятся.

Хотя у Andy и нет заказов, эта запись все равно отображается. Значение из второй таблицы равно NULL.

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

Все что мы сделали — нашли все значения NULL для order_id.

Отметим, что ключевое слово OUTER не обязательно. Вы можете использовать просто LEFT JOIN вместо LEFT OUTER JOIN.

Условия

Теперь давайте посмотрим на запросы с условиями.

Так, что случилось с Andy и Sandy? LEFT JOIN подразумевает, что мы должны получить покупателей, у которых нет заказов. Проблема в том, что условие WHERE скрывает эти результаты. Чтобы получить их, мы можем попытаться включить условие с NULL.

Появился Andy, но нет Sandy. Выглядит неправильно. Для того чтобы получить то, что мы хотим, нужно использовать предложение ON.

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

Right (Outer) Join (Правое внешнее соединение)

Объединение RIGHT OUTER JOIN работает также, только порядок таблиц меняется на обратный.

На этот раз мы не получили результатов с NULL, потому что каждый заказ имеет сопоставление с записью покупателя. Мы можем поменять порядок таблиц и получим тот же результат, что и с LEFT OUTER JOIN.

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

Заключение

Спасибо за чтение статьи. Надеюсь Вам понравилось!

Типы связей в реляционных базах данных

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

Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.

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

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

Только из данного краткого описания можно выделить несколько самостоятельных объектов:

• Телефонные линии обслуживания;
• Сотрудники отдела;
• Должности сотрудников;
• Группы, по которым распределены сотрудники;
• Звонки.

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

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

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

Всего существует 3 типа связей:

Примечание:
В данном материале обозначения связей приводятся на примере MS SQL Server. В иных СУБД они могут обозначаться по-разному, но у Вас не должно возникнуть проблем с определением их типа, т.к. они либо очень похожи, либо интуитивно понятны.

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

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

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

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

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

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

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

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

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

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

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

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

Если нескольким записям из одной таблицы соответствует несколько записей из другой таблицы, то такая связь называется «многие ко многим» и организовывается посредством связывающей таблицы.

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

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

Для чего все это нужно?

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

Правильно настроив связи, можно быть уверенным, что ничего не потеряется.

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