Поделиться через

Определение связей между таблицами с помощью Access SQL

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

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

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

Существует три типа связей:

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

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

При определении связей между таблицами необходимо сделать объявления CONSTRAINT на уровне поля. Это означает, что ограничения определяются в инструкции CREATE TABLE . Чтобы применить ограничения, используйте ключевое слово CONSTRAINT после объявления поля, назовите ограничение, назовите таблицу, на которую оно ссылается, и назовите поле или поля в этой таблице, которые будут создавать соответствующий внешний ключ.

В следующей инструкции предполагается, что таблица tblCustomers уже создана и имеет первичный ключ, определенный в поле CustomerID. Теперь инструкция создает таблицу tblInvoices, определяя ее первичный ключ в поле InvoiceID. Он также создает связь "один ко многим" между таблицами tblCustomers и tblInvoices путем определения другого поля CustomerID в таблице tblInvoices. Это поле определяется как внешний ключ, который ссылается на поле CustomerID в таблице customers. Обратите внимание, что имя каждого ограничения следует ключевому слову CONSTRAINT .

CREATE TABLE tblInvoices  
    (InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY, 
    CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID  
        REFERENCES tblCustomers (CustomerID), 
    InvoiceDate DATETIME, 
    Amount CURRENCY)

Обратите внимание, что индекс первичного ключа (PK_InvoiceID) для таблицы счетов объявляется в инструкции CREATE TABLE . Чтобы повысить производительность первичного ключа, для него автоматически создается индекс, поэтому нет необходимости использовать отдельную инструкцию CREATE INDEX . Теперь создайте таблицу доставки, которая будет содержать адрес доставки каждого клиента. Предположим, что для каждой записи клиента будет только одна запись доставки, поэтому вы будете устанавливать связь "один к одному".

CREATE TABLE tblShipping  
    (CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY 
        REFERENCES tblCustomers (CustomerID), 
    Address TEXT(50), 
    City TEXT(50), 
    State TEXT(2), 
    Zip TEXT(10))

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

Ограничения

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

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

Ограничение на одно поле, также известное как ограничение на уровне столбца, объявляется с самим полем после объявления поля и типа данных. Используйте таблицу customers и создайте первичный ключ с одним полем в поле CustomerID. Чтобы добавить ограничение, используйте ключевое слово CONSTRAINT с именем поля.

ALTER TABLE tblCustomers 
   ALTER COLUMN CustomerID INTEGER 
   CONSTRAINT PK_tblCustomers PRIMARY KEY

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

ALTER TABLE tblCustomers 
   ALTER COLUMN CustomerID INTEGER PRIMARY KEY

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

Чтобы удалить ограничение, используйте предложение DROP CONSTRAINT с инструкцией ALTER TABLE и укажите имя ограничения.

ALTER TABLE tblCustomers 
   DROP CONSTRAINT PK_tblCustomers

Ограничения также можно использовать для ограничения допустимых значений для поля. Значения можно ограничить значением NOT NULL или UNIQUE или определить проверочные ограничения, которые являются типом бизнес-правила, которое может применяться к полю. Предположим, что вы хотите ограничить (или ограничить) значения полей имени и фамилии, чтобы они были уникальными, а это означает, что сочетание имени и фамилии не должно быть одинаковым для любых двух записей в таблице. Так как это ограничение нескольких полей, оно объявляется на уровне таблицы, а не на уровне поля. Используйте предложение ADD CONSTRAINT и определите многополевой список.

ALTER TABLE tblCustomers 
   ADD CONSTRAINT CustomerID UNIQUE 
   ([Last Name], [First Name])

Проверочное ограничение — это мощная функция SQL, которая позволяет добавлять проверку данных в таблицу, создавая выражение, которое может ссылаться на одно поле или несколько полей в одной или нескольких таблицах. Предположим, что вы хотите убедиться, что суммы, указанные в записи счета, всегда больше 0,00 долл. США. Для этого используйте проверочные ограничения, объявив ключевое слово CHECK и выражение проверки в предложении ADD CONSTRAINT инструкции ALTER TABLE .

ALTER TABLE tblInvoices 
   ADD CONSTRAINT CheckAmount 
   CHECK (Amount > 0)

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

Предположим, что вы хотите проверить кредитный лимит каждого клиента, прежде чем он будет добавлен в таблицу клиентов. Используя инструкцию ALTER TABLE с предложениями ADD COLUMN и CONSTRAINT , создайте ограничение, которое будет искать значение в таблице CreditLimit для проверки кредитного лимита клиента. Используйте следующие инструкции SQL, чтобы создать таблицу tblCreditLimit, добавить поле CustomerLimit в таблицу tblCustomers, добавить проверочные ограничения в таблицу tblCustomers и протестировать проверочные ограничения.

CREATE TABLE tblCreditLimit ( 
   Limit DOUBLE) 
 
INSERT INTO tblCreditLimit 
   VALUES (100) 
 
ALTER TABLE tblCustomers 
   ADD COLUMN CustomerLimit DOUBLE 
 
ALTER TABLE tblCustomers 
   ADD CONSTRAINT LimitRule 
   CHECK (CustomerLimit <= (SELECT Limit 
      FROM tblCreditLimit)) 
 
UPDATE TABLE tblCustomers 
   SET CustomerLimit = 200 
   WHERE CustomerID = 1

Обратите внимание, что при выполнении инструкции UPDATE TABLE появляется сообщение о том, что обновление не выполнено, так как оно нарушило проверочные ограничения. Если обновить поле CustomerLimit до значения, равного 100 или меньшего значения, обновление будет выполнено успешно.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.