5. Таблицы MS Access
5.1. Создание таблиц
Таблицы — основные объекты БД. Без запросов, форм, отчетов и прочего можно обойтись, но если нет таблиц, то, значит, нет и базы данных.
Создание таблицы состоит в задании ее полей и назначении их свойств. Оно начинается с щелчка на кнопке «Создать» на вкладке «Таблицы»в окне базы данных, после чего откроется диалоговое окно, представленное на рисунке 5.1.1.
Как видно на рисунке 5.1.1, существует несколько способов создания новой таблицы.
- Импорт таблиц из другой базы, может быть, даже созданной в другой системе. В зависимости от обстоятельств из импортируемой таблицы может поступить структура полей, их названия и свойства, а также и содержимое базы. Если что-то импортируется не совсем так, как надо, необходимые правки (например, изменение свойств полей) вносят вручную.
- В тех случаях, когда речь идет о чужой таблице, которая находится на удаленном сервере и которую нельзя импортировать целиком, пользуются режимом Связь с таблицами. Это напоминает подключение к таблице для совместного использования ее данных.
- Использование Мастера таблиц. Мастер задает ряд вопросов и, руководствуясь полученными ответами, создает структуру таблицы автоматически. Несмотря на то что этот режим служит для упрощения работы, начинающим пользоваться им не рекомендуется, поскольку, не владея всей терминологией, легко запутаться в вопросах и ответах. Первые таблицы стоит попробовать создать вручную.
- Пункт Режим таблицы открывает заготовку, в которой все поля имеют формальные имена: Поле1, Поле2. и т. д. и один стандартный текстовый тип. Такую таблицу можно сразу наполнять информацией.
- Использование Конструктора. В этом режиме можно самостоятельно задать имена полей, выбрать их тип и настроить свойства, как показано на рисунке 5.1.2.
Редактирование таблицы возможно в двух режимах: «Режим таблицы» (можно просматривать и изменять непосредственно данные) и «Режим конструктора» (можно изменять свойства и структуру таблицы). При изменении структуры таблицы система управления базой данных всегда выдает запрос на сохранение изменений. Все изменения в данных таблицы сохраняются автоматически в режиме реального времени, т.е. как только заканчивается ввод данных в одно поле и происходит переход к следующему полю, данные немедленно записываются на жесткий диск. Поэтому, для экспериментов надо создавать отдельные копии базы или таблиц и работать с ними.
5.2. Типы данных в MS Access. Их использование и размеры
Тип данных
Когда используется
Размер
Числовые, денежные и логические типы данных, а также Дата/Время, обеспечивают определенные ранее форматы отображения. Для выбора форматов для каждого типа данных определите свойство «Формат поля». Также можно создать пользовательский формат отображения для всех данных, кроме объектов OLE.
5.3. Создание связей между таблицами в MS Access
Для создания связей между таблицами СУБД Access имеет специальное диалоговое окно, которое называется «Схема данных» (см. Рисунок 5.3.1).
* Примечание: Разумеется, одновременно несколько контекстных меню (контекстные меню вызываются нажатием правой кнопки «мыши») на экране отображаться не могут. На рисунке 5.3.1 несколько контестных меню представлены для наглядности.
Окно «Схема данных» открывают щелчком на одноименной кнопке панели инструментов или командой «Сервис» → » Схема данных». Если ранее никаких связей между таблицами базы не было, то при открытии окна «Схема данных» одновременно открывается окно «Добавление таблицы» (см. рисунок 5.3.2), в котором можно выбрать нужные таблицы для включения в структуру межтабличных связей. Если связи между таблицами уже были заданы, то для введения в схему данных новой таблицы надо щелкнуть правой кнопкой мыши на схеме данных и в контекстном меню выбрать пункт «Добавить таблицу».
Добавив в схему данных все таблицы, которые необходимо связать, можно приступать к созданию связей между полями таблиц. Связь между полями таблиц устанавливают путем перетаскивания имени поля из одной в таблицы в другую на соответствующее ему связанное поле, после чего открывается диалоговое окно «Связи», в котором можно задать свойства образующейся связи (см. рисунок 5.3.3).
Включение флажка «Обеспечение целостности данных «позволяет защититься от случаев удаления записей из одной таблицы, при которых связанные с ними данные других таблиц останутся без связи.
Термин Целостность данных означает систему правил, используемых в MS Access для поддержания связей между записями в связанных таблицах, а также обеспечивает защиту от случайного удаления или изменения связанных данных. Установить целостность данных можно, если выполнены следующие условия:
- Связанное поле главной таблицы является ключевым полем или имеет уникальный индекс.
- Связанные поля имеют один тип данных. Здесь существует два исключения. Поле счетчика может быть связано с числовым полем, если в последнем в свойстве «Размер поля «(Field Size) указано значение «Длинное целое». А также поле счетчика можно связать с числовым полем, если и в обеих ячейках свойства «Размер поля» (Field Size) задано значение «Код репликации».
- Обе таблицы принадлежат одной базе данных MS Access. Если таблицы являются связанными, то они должны быть таблицами MS Access. Для установки целостности данных база данных, в которой находятся таблицы, должна быть открыта. Для связанных таблиц из баз данных других форматов установить целостность данных стандартными средствами MS Access невозможно.
Установив целостность данных, необходимо следовать следующим правилам:
- Невозможно ввести в поле внешнего ключа связанной таблицы значение, не содержащееся в ключевом поле главной таблицы. Однако в поле внешнего ключа возможен ввод пустых значений, показывающих, что записи не являются связанными. Например, нельзя сохранить запись, регистрирующую заказ, сделанный несуществующим клиентом, но можно создать запись для заказа, который пока не отнесен ни к одному из клиентов, если ввести пустое значение в поле «Код Клиента».
- Не допускается удаление записи из главной таблицы, если существуют связанные с ней записи в подчиненной таблице. Например, невозможно удалить запись из таблицы «Сотрудники», если в таблице «Заказы» имеются заказы, относящиеся к данному сотруднику.
- Невозможно изменить значение ключевого поля в главной таблице, если существуют записи, связанные с данной. Например, невозможно изменить код сотрудника в таблице «Сотрудники», если в таблице «Заказы» имеются заказы, относящиеся к этому сотруднику.
Чтобы наложить эти правила на конкретную связь, при ее создании следует установить флажок . Если данный флажок установлен, то любая попытка выполнить действие, нарушающее одно из перечисленных выше правил, приведет к выводу на экран предупреждения, а само действие выполнено не будет.
Чтобы преодолеть ограничения на удаление или изменение связанных записей, сохраняя при этом целостность данных, следует установить флажки «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей». Если установлен флажок «Каскадное обновление связанных полей», то при изменении ключевого поля главной таблицы автоматически изменяются и соответствующие значения связанных записей. Если установлен флажок»Каскадное удаление связанных полей», то при удалении записи в главной таблице удаляются и все связанные записи в подчиненной таблице.
Источник
Определение связей между таблицами с помощью access SQL
Отношения — это установленные связи между двумя или более таблицами. Отношения основаны на общих полях из более чем одной таблицы, часто связанных с первичными и иностранными ключами.
Основным ключом является поле (или поля), которое используется для уникальной идентификации каждой записи в таблице. Для основного ключа существует три требования: он не может быть null, он должен быть уникальным, и в таблице может быть только одно. Основной ключ можно определить либо путем создания индекса основных ключевых элементов после создания таблицы, либо с помощью оговорки CONSTRAINT в декларации таблицы, как показано в примерах, ниже в этом разделе. Ограничение ограничивает (или ограничивает) значения, вступающие в поле.
Иностранный ключ — это поле (или поля) в одной таблице, которое ссылается на основной ключ в другой таблице. Данные в полях из обеих таблиц абсолютно одинаковы, а в таблице с основной записью ключей (основная таблица) должны быть существующие записи перед таблицей с записью иностранного ключа (иностранной таблицей) с соответствующими или связанными записями. Как и основные клавиши, в декларации таблицы можно определить внешние клавиши с помощью оговорки CONSTRAINT.
Существует по сути три типа отношений:
Один к одному Для каждой записи в основной таблице в иностранной таблице имеется только одна запись.
Один к многим Для каждой записи в основной таблице в иностранной таблице имеется одна или несколько связанных записей.
Много-много Для каждой записи в основной таблице имеется много связанных записей в иностранной таблице, а для каждой записи в иностранной таблице имеется много связанных записей в основной таблице.
Например, предположим, что вы хотите добавить таблицу счетов в базу данных счетов. Каждый клиент в таблице клиентов может иметь множество счетов-фактур , это классический сценарий. Вы можете взять основной ключ из таблицы клиентов и определить его как иностранный ключ в таблице счетов, тем самым устанавливая правильную связь между таблицами.
При определении связей между таблицами необходимо сделать объявления CONSTRAINT на уровне поля. Это означает, что ограничения определяются в заявлении CREATE TABLE. Чтобы применить ограничения, используйте ключевое слово CONSTRAINT после объявления поля, назови ограничение, назови таблицу, на которую ссылается, и назови поле или поля в этой таблице, которые будут соответствовать иностранному ключу.
В следующем заявлении предполагается, что таблица tblCustomers уже построена и что она имеет основной ключ, определенный в поле CustomerID. Теперь в заявлении создается таблица tblInvoices, определяющая ее основной ключ в поле InvoiceID. Кроме того, создается связь между таблицами tblCustomers и tblInvoices, определяя другое поле CustomerID в таблице tblInvoices. Это поле определяется как иностранный ключ, который ссылается на поле CustomerID в таблице клиентов. Обратите внимание, что имя каждого ограничения следует ключевому слову CONSTRAINT.
Обратите внимание, что основной индекс ключа (PK_InvoiceID) для таблицы счетов объявляется в заявлении CREATE TABLE. Чтобы повысить производительность основного ключа, для него автоматически создается индекс, поэтому нет необходимости использовать отдельное заявление CREATE INDEX. Теперь создайте таблицу доставки, которая будет содержать адрес доставки каждого клиента. Предположим, что для каждой записи клиента будет иметься только одна запись доставки, поэтому будет устанавливаться отношение один к одному.
Обратите внимание, что поле CustomerID является как основным ключом для таблицы доставки, так и ссылкой на иностранный ключ в таблице клиентов.
Ограничения
Ограничения можно использовать для создания основных ключей и целостности ссылок, а также для ограничения значений, которые можно вставить в поле. В общем, ограничения можно использовать для сохранения целостности и согласованности данных в базе данных.
Существует два типа ограничений: ограничение на уровне одного поля или поля и ограничение на уровне нескольких полей или таблиц. Оба типа ограничений можно использовать в заявлении CREATE TABLE или ALTER TABLE.
Ограничение на одно поле, также известное как ограничение на уровне столбцов, объявляется с самим полем после того, как было объявлено поле и тип данных. Используйте таблицу клиентов и создайте основной ключ с одним полем в поле CustomerID. Чтобы добавить ограничение, используйте ключевое слово CONSTRAINT с именем поля.
Обратите внимание, что имя ограничения дано. Можно использовать ярлык для объявления основного ключа, полностью отметаемого в пункте CONSTRAINT.
Однако использование метода ярлыка приведет к случайному сгенерировать имя ограничения, что затруднит ссылку в коде. Всегда стоит назвать ограничения.
Чтобы снять ограничение, используйте пункт DROP CONSTRAINT с заявлением ALTER TABLE и укай имя ограничения.
Ограничения также могут использоваться для ограничения допустимых значений для поля. Можно ограничить значения NOT NULL или UNIQUE или определить ограничение проверки, которое является типом бизнес-правила, которое можно применить к полю. Предположим, что необходимо ограничить (или ограничить) значения полей имени и фамилии уникальными, что означает, что никогда не должно быть сочетания имени и фамилии, одинаковой для любых двух записей в таблице. Поскольку это ограничение с несколькими полями, оно объявляется на уровне таблицы, а не на уровне поля. Используйте пункт ADD CONSTRAINT и определите список с несколькими полями.
Ограничение проверки — это мощная SQL, которая позволяет добавлять проверку данных в таблицу, создавая выражение, которое может ссылаться на одно поле или несколько полей в одном или нескольких таблицах. Предположим, что необходимо убедиться, что суммы, вписаные в запись счетов, всегда больше $ 0,00. Для этого используйте ограничение проверки, объявив ключевое слово CHECK и выражение проверки в пункте ADD CONSTRAINT из заявления ALTER TABLE.
Выражение, используемое для определения ограничения проверки, также может относиться к более чем одному полю в одной таблице или к полям в других таблицах и может использовать любые операции, допустимые в access SQL, такие как операторы SELECT, математические операторы и агрегированные функции. Выражение, определяющие ограничение проверки, может быть не более 64 символов.
Предположим, что необходимо проверить кредитный лимит каждого клиента, прежде чем он будет добавлен в таблицу клиентов. Используя заявление ALTER TABLE с оговорками ADD COLUMN и CONSTRAINT, создайте ограничение, которое будет проверять значение в таблице CreditLimit для проверки кредитного лимита клиента. Используйте следующие SQL для создания таблицы tblCreditLimit, добавьте поле CustomerLimit в таблицу tblCustomers, добавьте ограничение проверки в таблицу tblCustomers и проверьте ограничение проверки.
Обратите внимание, что при выполнении заявления UPDATE TABLE вы получаете сообщение о том, что обновление не удалось, так как оно нарушило ограничение проверки. Если вы обновим поле CustomerLimit до значения, равного или менее 100, обновление будет успешным.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Источник