Определение связей между таблицами в базе данных Access

Сводка

В этой статье описывается определение связей в базе данных Microsoft Access. В этой статье описываются:

• Что такое связи таблиц?
• Типы связей таблиц
  • Связи "один ко многим"
  • Отношения «многие ко многим»
  • Связи "один к одному"
• Определение связей между таблицами
  • Как определить связь "один ко многим" или "один к одному"
  • Как определить связь "многие ко многим"
• Ссылочная целостность
• Каскадные обновления и удаления
• Типы соединения

Что такое связи таблиц?

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

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

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

Логические связи в базе данных позволяют эффективно запрашивать данные и создавать отчеты.

Типы связей таблиц

Связь работает путем сопоставления данных в ключевых столбцах, обычно столбцы (или поля), имеющие одинаковое имя в обеих таблицах. В большинстве случаев связь подключает первичный ключ или уникальный столбец идентификатора для каждой строки из одной таблицы к полю в другой таблице. Столбец в другой таблице называется внешним ключом. Например, если вы хотите отслеживать продажи каждого названия книги, вы создадите связь между первичным ключевым столбцом (давайте называем его title_ID) в таблице "Titles" и столбцом в таблице "Продажи", которая называется title_ID. Столбец title_ID в таблице Sales является внешним ключом.

Между таблицами существует три типа связей. Тип создаваемой связи зависит от того, как определены связанные столбцы.

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

Связь "один ко многим" является наиболее распространенным типом отношений. В этом роде связи строка в таблице A может содержать множество соответствующих строк в таблице B. Но строка в таблице B может иметь только одну соответствующую строку в таблице A. Например, таблицы Publishers и Titles имеют отношение "один ко многим". То есть каждый издатель создает множество названий. Но каждый заголовок поступает только от одного издателя.

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

В окне связи Access первичный ключ стороны связи "один ко многим" обозначается числом 1. Сторона отношения с внешним ключом обозначается символом бесконечности.

Отношения «многие ко многим»

В отношении "многие ко многим" запись в таблице A может иметь много соответствующих записей в таблице B, и наоборот. Вы создадите эту связь, определив третью таблицу, которая называется таблицей соединения. Первичный ключ таблицы связей состоит из внешних ключей из таблицы А и таблицы B. Например, таблица "Авторы" и таблица "Заголовки" имеют связь "многие ко многим", определяемую связью "один ко многим" из каждой из этих таблиц в таблицу "ЗаголовкиАвторов". Первичный ключ таблицы TitleAuthors — это сочетание столбца au_ID (первичный ключ таблицы "Авторы") и столбец title_ID (первичный ключ таблицы "Titles").

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

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

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

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

В Access первичная сторона связи "один к одному" обозначается символом ключа. Сторона внешнего ключа также обозначается иконкой ключа.

Определение связей между таблицами

При создании связи между таблицами связанные поля не должны иметь одинаковые имена. Однако связанные поля должны иметь один и тот же тип данных, если поле первичного ключа не является полем AutoNumber. Поле AutoNumber можно сопоставить с полем Number только в том случае, если свойствоFieldSizeproperty обоих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство размера поля (FieldSize) обоих полей имеет значение длинное целое число. Даже если оба совпадающих поля являются полями Number, они должны иметь одинаковый параметрFieldSizeproperty.

Как определить связь "один ко многим" или "один к одному"

Чтобы создать связь "один ко многим" или "один к одному", выполните следующие действия:

1. Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.

2. В Access 2002 или Access 2003 выполните следующие действия:

   1. Нажмите клавишу F11, чтобы переключиться в окно базы данных.
   2. В меню "Сервис" щелкните "Связи".

   В Access 2007, Access 2010 или Access 2013 щелкните "Связи " на вкладке " Показать и скрыть " на вкладке "Инструменты баз данных ".

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

4. Дважды щелкните имена таблиц, которые нужно связать, и закройте диалоговое окно "Показать таблицу ". Чтобы создать связь между таблицей и самой собой, добавьте эту таблицу два раза.

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

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

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

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

7. Нажмите кнопку "Создать" , чтобы создать связь.

8. Повторите шаги 4–7 для каждой пары таблиц, которые необходимо связать.

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

   Замечание

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

Как определить связь "многие ко многим"

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

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

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

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

   Замечание

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

   1. Откройте таблицу в представлении конструктора.

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

   3. В Access 2002 или Access 2003 щелкните первичный ключ на панели инструментов.

      В Access 2007 щелкните Primary Key в группе «Сервис» на вкладке «Конструктор».

      Замечание

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

4. Определите связь "один ко многим" между каждой основной таблицей и связывающей таблицей.

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

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

• Соответствующее поле из первичной таблицы является первичным ключом или имеет уникальный индекс.
• Связанные поля имеют один и тот же тип данных. Существует два исключения. Поле autoNumber может быть связано с полем Number с параметром FieldSize свойства Long Integer, а поле AutoNumber с FieldSize параметром свойства идентификатора репликации может быть связано с полем Number с параметром FieldSize свойства идентификатора репликации.
• Обе таблицы принадлежат одной базе данных Access. Если таблицы являются связанными таблицами, они должны быть таблицами в формате Access, и необходимо открыть базу данных, в которой они хранятся, чтобы задать целостность ссылок. Целостность ссылок не может быть применена для связанных таблиц из баз данных в других форматах.

При использовании целостности ссылок применяются следующие правила:

• Нельзя ввести значение в поле внешнего ключа связанной таблицы, которая не существует в первичном ключе первичной таблицы. Однако в внешний ключ можно ввести значение NULL. Это свидетельствует о том, что записи не взаимосвязаны. Например, у вас не может быть заказ, назначенный клиенту, который не существует. Однако у вас может быть заказ, которому никто не назначен, введя значение NULL в поле CustomerID .
• Невозможно удалить запись из первичной таблицы, если соответствующие записи существуют в связанной таблице. Например, нельзя удалить запись сотрудника из таблицы "Сотрудники", если в таблице "Заказы" есть заказы, назначенные этому сотруднику.
• Невозможно изменить значение первичного ключа в первичной таблице, если эта запись имеет связанные записи. Например, нельзя изменить идентификатор сотрудника в таблице "Сотрудники", если этому сотруднику назначены заказы в таблице "Заказы".

Каскадные обновления и удаления

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

Если щелкнуть флажок "Каскадное обновление связанных полей " при определении связи, при изменении первичного ключа записи в первичной таблице Microsoft Access автоматически обновляет первичный ключ до нового значения во всех связанных записях. Например, если изменить идентификатор клиента в таблице "Клиенты", поле CustomerID в таблице "Заказы" автоматически обновляется для каждого из заказов этого клиента, чтобы связь не была нарушена. Доступ к каскадным обновлениям без отображения сообщения.

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

Типы соединения

Существует три типа соединения. Их можно увидеть на следующем снимке экрана:

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

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

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