СУБД MS Access. Ключевые поля. Определение и редактирование связей между таблицами.

База данных – это организационная структура, предназначенная для хранения информации.

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

Access – это реляционная система управления базами данных (СУБД), входящая в пакет MS Office.

Все составляющие базы данных, такие, как таблицы, отчеты, запросы, формы и объекты, в Access хранятся в едином дисковом файле, который имеет расширение .mdb.

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

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

Свойства полей базы данных Access

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

Типы данных Access

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

В Microsoft Access можно выделить три типа ключевых полей: счетчик, простой ключ и составной ключ. Рассмотрим каждый из этих типов.

Для создания ключевого поля типа Счетчик необходимо в режиме Конструктора таблиц:

1. Включить в таблицу поле счетчика.
2. Задать для него автоматическое увеличение на 1.
3. Указать это поле в качестве ключевого путем нажатия на кнопку Ключевое поле (Primary Key) на панели инструментовКонструктор таблиц (Table Design).

Если до сохранения созданной таблицы ключевые поля не были определены, то при сохранении будет выдано сообщение о создании ключевого поля. При нажатии кнопки Да (Yes) будет создано ключевое поле счетчика с именем Код (ID) и типом данных Счетчик (AutoNumber).

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

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

1. Открыть таблицу в режиме Конструктора.
2. Выделить поля, которые необходимо определить как ключевые.
3. Нажать кнопку Ключевое поле (Primary Key) на панели инструментов Конструктор таблиц (Table Design).

Замечание

Для составного ключа существенным может оказаться порядок образующих ключ полей. Сортировка записей осуществляется в соответствии с порядком ключевых полей в окне Конструктора таблицы. Если необходимо указать другой порядок сортировки без изменения порядка ключевых полей, то сначала нужно определить ключ, а затем нажать кнопку Индексы (Indexes) на панели инструментов Конструктор таблиц (Table Design). Затем в появившемся окне Индексы (Indexes) нужно указать другой порядок полей для индекса с именем Ключевое поле (Primary Key).

Рассмотрим в качестве примера применения составного ключа таблицу "Заказано" (OrderDetails) базы данных (Northwind) (рис. 2.23).

В данном случае в качестве составного ключа используются поля "Код заказа" (OrderlD) и "КодТовара" (ProductID), т. к. ни одно из этих полей в отдельности не гарантирует уникальность записи. При этом в таблице выводится не код товара, а наименование товара, т. к. поле "КодТовара" (ProductID) данной таблицы содержит подстановку из таблицы "Товары" (Products), а значения полей "КодТовара" (ProductID) этих таблиц связаны отношением "один-ко-многим" (одной записи таблицы "Товары" (Products) может соответствовать несколько записей таблицы "Заказано" (OrderDetails)). Оба поля могут содержать повторяющиеся значения. Так, один заказ может включать в себя несколько товаров, а в разные заказы могут включаться одинаковые товары. В то же время сочетание полей "КодЗаказа" (OrderlD) и "КодТовара" (ProductID) однозначно определяет каждую запись таблицы "Заказы" (OrderDetails).

Чтобы изменить ключ, необходимо:

1. Открыть таблицу в режиме Конструктора.
2. Выбрать имеющиеся ключевые поля.
3. Нажать на кнопку Ключевое поле (Primary Key), при этом кнопка должна принять положение Выкл., а из области выделения должны исчезнуть значки ключевого поля.
4. Выбрать поле, которое необходимо сделать ключевым.
5. Нажать на кнопку Ключевое поле (Primary Key). При этом в области выделения должен появиться значок ключевого поля.

Рис. 2.23. Пример таблицы с использованием составного ключа

Чтобы удалить ключ, необходимо:

1. Открыть таблицу в режиме Конструктора.
2. Выбрать имеющееся ключевое поле (ключевые поля).
3. Нажать на кнопку Ключевое поле (Primary Key), при этом кнопка должна принять положение Выкл., а из области выделения должен исчезнуть значок (значки) ключевого поля.

Что такое связи между таблицами

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

Лучшим решением является хранение информации об издателе в отдельной таблице Publishers. Затем следует установить указатель в таблице Titles, на запись в таблице Publishers.
 

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

Типы связей между таблицами

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

Существует три типа связей между таблицами.

Отношения один ко многим

Отношение один ко многим является наиболее распространенным типом связи. 

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

В Microsoft Access сторона первичного ключа отношения «один ко многим» обозначается символом ключа.

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

В отношение многие ко многим строке в таблице A может иметь несколько совпадающих строк таблицы Б и наоборот.

Отношения один к одному

Связи «один к одному» строке в таблице A может иметь не более одной соответствующей строки в таблице B и наоборот. 

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

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

В Microsoft Access сторона первичного ключа в связи «один к одному» обозначается символом ключа.

Создание связей между таблицами

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

Определение отношения один ко многим "или" один к одному

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

1. Закройте все открытые таблицы. Нельзя создавать или изменять связи между открытыми таблицами.
2. Нажмите клавишу F11, чтобы перейти в окно базы данных.
3. В меню Сервис выберите пункт связи.
4. Если в базе данных отсутствуют связи, автоматически отображается диалоговое окно Добавление таблицы . Если вы хотите добавить таблицы, которые необходимо связать, но не отображается в диалоговом окне Добавление таблицы , нажмите кнопку Отобразить таблицу меню связи .
5. Дважды щелкните имена таблиц, которые требуется связать, а затем закройте диалоговое окно Добавление таблицы . Чтобы создать отношение между таблицей и сам, добавьте ее дважды.
6. Перетащите поле, которое необходимо связать с одной таблицы на соответствующее поле в другой таблице.Чтобы перетащить несколько полей, удерживайте клавишу CTRL, выберите каждое поле и затем перетащите их.
   
   В большинстве случаев перетащите поле первичного ключа (который отображается полужирным текстом) из одной таблицы на аналогичное поле (часто с тем же именем) называется внешнего ключа в другой таблице.
7. Откроется диалоговое окно Изменение связей . Проверьте правильность имен полей, присутствующих в двух столбцах. При необходимости их можно изменить. 
   
   При необходимости установите параметры связи. Для получения сведений о конкретном параметре в диалоговом окне « Изменение связей », нажмите кнопку с вопросительным знаком и щелкните элемент. Эти параметры будут описаны подробно далее в этой статье.
8. Нажмите кнопку Создать , чтобы создать связь.
9. Повторите шаги с 5 по 8 для каждой пары таблиц, которые необходимо связать.
   
   При закрытии диалогового окна Изменение связей , Microsoft Access спросит, нужно ли сохранить макет. Ли или не сохранить макет, отношений, сохраняются в базе данных.
   
   Примечание: В запросах, а также таблицы, можно создавать связи. Тем не менее ссылочная целостность не обеспечивается с запросами.

Как определить отношения многие ко многим

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

1. Создайте две таблицы, которые будут иметь отношение многие ко многим.
2. Создание третьей таблицы, называемой связующей таблицей и затем добавить в поля с теми же определениями, как поля первичного ключа в каждой из двух связываемых таблиц. Поля первичных ключей в сводной таблице, служат внешними ключами. Так же, как и для любой другой таблицы, можно добавить другие поля в связующую таблицу.
3. В сводной таблице задайте первичный ключ для включения поля первичных ключей из двух связываемых таблиц.Например в связующей таблице TitleAuthors первичный ключ может состоять из поля OrderID и ProductID. 
   
   Примечание: чтобы создать первичный ключ, выполните следующие действия:
   1. Откройте таблицу в режиме конструктора.
   2. Выберите поле или поля, которые необходимо определить как первичный ключ. Для выделения одного поля щелкните область выделения строки для нужного поля.
      
      Чтобы выбрать несколько полей, удерживайте нажатой клавишу CTRL и щелкните область выделения строки для каждого поля.
   3. Нажмите кнопку Ключевое поле на панели инструментов. 
      
      Примечание: Если порядок полей в нескольких полей первичного ключа должно отличаться от порядка полей в таблице, нажмите кнопку индексы на панели инструментов, чтобы открыть диалоговое окноиндексов , а затем порядок полей для индекса с именем PrimaryKey.
4. Определите отношение "один ко многим" между каждой из двух главных таблиц и связующей таблицей.

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

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

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

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

 

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

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

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

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

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

Если при определении отношения установить флажок Каскадное удаление связанных записей , при каждом удалении записи в главной таблице Microsoft Access автоматически удалит связанные записи в связанной таблице. Например при удалении записи клиента из таблицы «Клиенты» всех заказов этого клиента автоматически удаляются из таблицы Orders (включая записи в таблице Order Details, связанные с записями заказов). При удалении записей из формы или таблицы с установленным флажком Каскадное удаление связанных записей , Microsoft Access предупреждает, что связанные записи также будут удалены. Однако при удалении записей с помощью запроса на удаление Microsoft Access автоматически удалит записи из связанных таблиц без вывода предупреждения.

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

Существует три типа соединения, как показано ниже:

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

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

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