§ 11. Связывание таблиц базы данных

Определения и обозначения

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

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

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

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

Как это устроено

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

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

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

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

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

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

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

Разбор задач

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

1. Находим первичный ключ таблицы «Категории», например «КодКатегории»: он отличает одну категорию от другой.
2. В таблице «Товары» добавляем поле «КодКатегории» с совместимым типом данных: его значения будут ссылаться на ключ категории.
3. Проверяем, что каждому указанному коду соответствует существующая категория; один и тот же код может встречаться у многих товаров.

Ответ: таблицы связывают по коду категории. Внешний ключ «КодКатегории» размещают в таблице «Товары», потому что товары находятся на стороне «многие».

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

1. Определяем первичный ключ таблицы «Читатели», например «КодЧитателя».
2. Добавляем в «Выдачи» поле «КодЧитателя» как внешний ключ; каждая выдача получает код того читателя, которому выдали книгу.
3. Чтобы найти выдачи нужного человека, сначала берём его код из «Читатели», а затем отбираем в «Выдачи» записи с тем же значением внешнего ключа.

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

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

1. Создаём таблицу «Ученики» с первичным ключом «КодУченика» и таблицу «Кружки» с первичным ключом «КодКружка».
2. Добавляем промежуточную таблицу «Участие» с полями «КодУченика» и «КодКружка»; оба поля являются внешними ключами.
3. Для каждого факта участия добавляем отдельную пару кодов, проверяя, что ученик и кружок существуют. При необходимости в этой же таблице можно хранить дату начала занятий.

Ответ: таблицы «Ученики» и «Кружки» связываются через «Участие». Внешние ключи в промежуточной таблице указывают на ученика и кружок, образуя пары соответствующих записей.

Типичные ошибки

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

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

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

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

Связь с другими темами

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

Что запомнить

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