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

Изменение структуры таблицы с помощью Access SQL

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

С помощью инструкции ALTER TABLE можно добавить, удалить или изменить столбец (или поле), а также добавить или удалить ограничение. Можно также объявить значение по умолчанию для поля; однако одновременно можно изменять только одно поле. Предположим, что у вас есть база данных счетов и вы хотите добавить поле в таблицу Customers. Чтобы добавить поле с инструкцией ALTER TABLE , используйте предложение ADD COLUMN с именем поля, его типом данных и размером типа данных, если это необходимо.

ALTER TABLE tblCustomers 
   ADD COLUMN Address TEXT(30)

Чтобы изменить тип данных или размер поля, используйте предложение ALTER COLUMN с именем поля, требуемым типом данных и требуемым размером типа данных, если это необходимо.

ALTER TABLE tblCustomers 
   ALTER COLUMN Address TEXT(40)

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

ALTER TABLE tblCustomers 
   DROP COLUMN Address

Обратите внимание, что при использовании этого метода будут устранены существующие данные для поля. Чтобы сохранить существующие данные, необходимо изменить имя поля в режиме конструктора таблицы пользовательского интерфейса Access или написать код для сохранения текущих данных во временной таблице и добавить их обратно в переименованную таблицу. Значение по умолчанию — это значение, которое вводится в поле каждый раз при добавлении новой записи в таблицу и не указано значение для этого конкретного столбца. Чтобы задать значение по умолчанию для поля, используйте ключевое слово DEFAULT после объявления типа поля в предложении ADD COLUMN или ALTER COLUMN .

ALTER TABLE tblCustomers 
   ALTER COLUMN Address TEXT(40) DEFAULT Unknown

Имейте в виду, что значение по умолчанию не заключено в одинарные кавычки. Если бы это было так, кавычки также были бы вставлены в запись. Ключевое слово DEFAULT также можно использовать в инструкции CREATE TABLE .

CREATE TABLE tblCustomers ( 
   CustomerID INTEGER CONSTRAINT PK_tblCustomers 
      PRIMARY KEY, 
   [Last Name] TEXT(50) NOT NULL, 
   [First Name] TEXT(50) NOT NULL, 
   Phone TEXT(10), 
   Email TEXT(50), 
   Address TEXT(40) DEFAULT Unknown)

Примечание.

Инструкцию DEFAULT можно выполнить только через поставщик OLE DB Access и ADO. При использовании через пользовательский интерфейс Access SQL View возвращается сообщение об ошибке.

Ограничения

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

Существует два типа ограничений: ограничение на уровне одного поля или на уровне поля и ограничение на уровне нескольких полей или таблиц. Оба типа ограничений можно использовать в инструкции 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)

Выражение, используемое для определения проверочного ограничения, также может ссылаться на несколько полей в одной таблице или на поля в других таблицах, а также может использовать любые операции, допустимые в Microsoft 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 или меньшего значения, обновление будет выполнено успешно.

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

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

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

Чтобы включить каскадные обновления и удаления, используйте ключевые слова ON UPDATE CASCADE и (или ) ON DELETE CASCADE в предложении CONSTRAINT инструкции ALTER TABLE . Имейте в виду, что они должны применяться к внешнему ключу.

ALTER TABLE tblShipping 
   ADD CONSTRAINT FK_tblShipping 
   FOREIGN KEY (CustomerID) REFERENCES 
      tblCustomers (CustomerID) 
   ON UPDATE CASCADE 
   ON DELETE CASCADE

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

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