Предложение CONSTRAINT (Microsoft Access SQL)
Ограничение похоже на индекс, несмотря на то, что его можно также использовать для создания связи с другой таблицей.
Вы можете использовать предложение CONSTRAINT в операторах ALTER TABLE и CREATE TABLEдля создания или удаления ограничений. Существует два типа предложений CONSTRAINT: одно для создания ограничения на отдельное поле, а другое — для создания ограничения на несколько полей.
Ядро СУБД Access не поддерживает использование CONSTRAINT или любые инструкции DDL с базами данных, которые не являются базами данных Microsoft Access. Используйте вместо этого методы DAO Create.
Синтаксис
Ограничения одного поля
ИМЯ ОГРАНИЧЕНИЯ foreigntable [(foreignfield1, foreignfield2)] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL]>
Ограничения нескольких полей
CONSTRAINT name primary1[, primary2 [, . ]]]) | UNIQUE (unique1[, unique2 [, . ]]]) | NOT NULL (notnull1[, notnull2 [, . ]]) | FOREIGN KEY [NO INDEX] (ref1[, ref2 [, . ]]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, . ]])] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL]>
Предложение CONSTRAINT состоит из следующих частей:
Имя ограничения, которое необходимо создать.
Имя поля или полей, которые будут являться первичным ключом.
Имя поля или полей, которые будут являться уникальным ключом.
Имя поля или полей, в которых допускаются только значения, отличные от Null.
Имя поля или полей внешнего ключа, который ссылается на поля в другой таблице.
Имя внешней таблицы, содержащей одно или несколько полей, заданных foreignfield.
Имя поля или полей в foreigntable, определяемых ref1, ref2. Это предложение можно опустить, если поле, на которое ссылаются, представляет собой первичный ключ foreigntable.
Комментарии
Используйте синтаксис для ограничения одного поля в предложении, определяющем поле, инструкции ALTER TABLE или CREATE TABLE непосредственно после спецификации типа данных поля.
Используйте данный синтаксис для ограничения нескольких полей в случае, когда вы используете зарезервированное слово CONSTRAINT за пределами определяющего поле предложения в операторе ALTER TABLE или CREATE TABLE.
С помощью CONSTRAINT можно назначить поле в качестве одного из следующих типов ограничений:
- Чтобы указать поле в качестве уникального ключа, можно использовать зарезервированное слово UNIQUE. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Вы можете определить любое поле или поля как уникальные. Если ограничение для нескольких полей используется в качестве уникального ключа, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.
- С помощью зарезервированного слова PRIMARY KEY можно назначить одно поле или набор полей в таблице в качестве первичного ключа. Все значения в первичном ключе должны быть уникальными и не быть равными Null, а у таблицы может быть только один первичный ключ.
Примечание. Не используйте ограничение PRIMARY KEY в таблице, в которой уже есть первичный ключ; если вы сделаете это, возникнет ошибка.
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))
Рассмотрим следующее определение таблицы «Заказы», которое определяет отношение внешнего ключа, ссылающегося на первичный ключ таблицы «Клиенты»:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
Предложения ON UPDATE CASCADE и ON DELETE CASCADE определяются для внешнего ключа. Предложение ON UPDATE CASCADE означает, что если в таблице «Клиенты» обновляется идентификатор клиента (CustId), будет выполняться каскадное обновление таблицы «Заказы». Каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически обновляться новым значением. Предложение ON DELETE CASCADE означает, что если клиент удаляется из таблицы «Клиенты», все строки в таблице «Заказы», содержащие то же значение идентификатора клиента, также будут удалены. Рассмотрим другое определение таблицы «Заказы», в котором вместо действия CASCADE используется SET NULL:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL
Чтобы предотвратить автоматическое создание индексов для внешних ключей, можно использовать модификатор NO INDEX. Данная форма определения внешнего ключа должна применяться только в случаях, где получаемые в результате значения индекса часто будут дублироваться. Если значения в индексе внешнего ключа часто дублируются, использование индекса может быть менее эффективным, чем простое выполнение сканирования таблицы. Использование данного типа индекса со строками, вставляемыми и удаляемыми из таблицы, ухудшает производительность и не дает никаких преимуществ.
Пример
В этом примере создается новая таблица с именем ThisTable и двумя текстовыми полями.
Sub CreateTableX1() Dim dbs As Database ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Create a table with two text fields. dbs.Execute "CREATE TABLE ThisTable " _ & "(FirstName CHAR, LastName CHAR);" dbs.Close End Sub
В этом примере создается новая таблица с именем MyTable с двумя текстовыми поля, полем даты и времени и уникальным индексом, состоящим из всех трех полей.
Sub CreateTableX2() Dim dbs As Database ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Create a table with three fields and a unique ' index made up of all three fields. dbs.Execute "CREATE TABLE MyTable " _ & "(FirstName CHAR, LastName CHAR, " _ & "DateOfBirth DATETIME, " _ & "CONSTRAINT MyTableConstraint UNIQUE " _ & "(FirstName, LastName, DateOfBirth));" dbs.Close End Sub
В этом примере создается новая таблица с двумя текстовыми полями и полем Integer. Поле SSN является первичным ключом.
Sub CreateTableX3() Dim dbs As Database ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Create a table with three fields and a primary ' key. dbs.Execute "CREATE TABLE NewTable " _ & "(FirstName CHAR, LastName CHAR, " _ & "SSN INTEGER CONSTRAINT MyFieldConstraint " _ & "PRIMARY KEY);" dbs.Close End Sub
Предложение CONSTRAINT
Таблицы ограничение аналогичны индекс, однако их также можно использовать для создания связи отношение другой таблицей.
Предложение CONSTRAINT используется в инструкциях ALTER TABLE и CREATE TABLE для создания и удаления ограничений. Существует два типа предложений CONSTRAINT: для создания ограничения на одно поле и на несколько полей.
Примечание: Ядро СУБД Microsoft Access не поддерживает использование CONSTRAINT или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого применяйте методы Create DAO.
Синтаксис
Ограничение на одно поле:
CONSTRAINT имя REFERENCES внешняя_таблица [(внешнее_поле1, внешнее_поле2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Ограничение на несколько полей:
CONSTRAINT имя
первичный_ключ1[, первичный_ключ2 [, . ]]) |
UNIQUE (уникальный_ключ1[, уникальный_ключ2 [, . ]]) |
NOT NULL (непустое1[, непустое2 [, . ]]) |
FOREIGN KEY [NO INDEX] (ссылка1[, ссылка2 [, . ]]) REFERENCES внешняя_таблица [(внешнее_поле1 [, внешнее_поле2 [, . ]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Предложение CONSTRAINT включает в себя следующие элементы:
Имя создаваемого ограничения.
первичный_ключ1, первичный_ключ2
Имена полей, определяемых как первичный ключ.
уникальный_ключ1, уникальный_ключ2
Имена полей, определяемых как уникальный ключ.
непустое1, непустое2
Имена полей, в которых не может быть значений NULL.
ссылка1, ссылка2
Имя поля или полей внешнего ключа, которые ссылаются на поля в другой таблице.
внешняя_таблица
Имя поля внешняя таблица которое содержит поле или поля, заданные полем внешнего поля.
внешнее_поле1, внешнее_поле2
Имена полей во внешней_таблице, заданных аргументами ссылка1, ссылка2. Если ссылка указывает на поле первичного ключа внешней_таблицы, данный аргумент можно опустить.
Замечания
Выражение для ограничения на одно поле указывается в предложении определения поля инструкции ALTER TABLE или CREATE TABLE непосредственно за спецификацией типа данных поля.
Выражение для ограничения на несколько полей указывается тогда, когда зарезервированное слово CONSTRAINT используется вне предложения определения поля в инструкции ALTER TABLE или CREATE TABLE.
В предложении CONSTRAINT можно задать для поля один из следующих типов ограничений:
- Зарезервированное слово UNIQUE используется для назначения поля в качестве уникального ключа. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Любое поле (или список полей) можно ограничить как уникальное. Если ограничение на несколько полей назначено уникальным ключом, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.
- Зарезервированные слова PRIMARY KEY используются для назначения одного поля или совокупности полей таблицы в качестве первичного ключа. Все значения в первичном ключе должны быть уникальными и отличными от NULL, причем в таблице может быть только один первичный ключ.
Примечание: Ограничение PRIMARY KEY не следует устанавливать в таблице, уже имеющей первичный ключ: это приведет к ошибке.
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))
Пример определения для таблицы Orders, в котором задается отношение внешнего ключа, ссылающееся на первичный ключ таблицы Customers:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
Для внешнего ключа определены предложения ON UPDATE CASCADE и ON DELETE CASCADE. Предложение ON UPDATE CASCADE означает, что при обновлении в таблице Customers идентификатора клиента (CustId) будет выполнено каскадное обновление в таблице Orders. В каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически внесено новое значение. Предложение ON DELETE CASCADE означает, что при удалении клиента из таблицы Customers все строки таблицы Orders, содержащие его идентификатор, также будут удалены.
Другое определение для таблицы Orders, в котором вместо действия CASCADE используется SET NULL:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL
Предложение ON UPDATE SET NULL означает, что при обновлении в таблице Customers идентификатора клиента (CustId) соответствующие значения внешнего ключа в таблице Orders автоматически изменятся на NULL. Аналогично предложение ON DELETE SET NULL означает, что при удалении клиента из таблицы Customers все соответствующие внешние ключи в таблице Orders принимают значение NULL.
Чтобы предотвратить автоматическое создание индексов для внешних ключей, можно использовать модификатор NO INDEX. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.
SQL ADD CONSTRAINT Ключевое слово
Команда ADD CONSTRAINT используется для создания ограничения после того, как таблица уже создана.
Следующий SQL добавляет ограничение с именем «PK_Person», которое является ограничением первичного ключа для нескольких столбцов (ID и фамилия):
Пример
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
DROP CONSTRAINT — Удалить ограничение
Команда DROP CONSTRAINT используется для удаления уникального, первичного ключа, внешнего ключа или ограничения проверки.
Удалить уникальное ограничение
Чтобы удалить уникальное ограничение, используйте следующий SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
ALTER TABLE Persons
DROP INDEX UC_Person;
Удалить ограничение первичного ключа
Чтобы удалить ограничение первичного ключа, используйте следующий SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
ALTER TABLE Persons
DROP PRIMARY KEY;
Удалить ограничения внешнего ключа
Чтобы удалить ограничение внешнего ключа, используйте следующий SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
Удалить ограничение проверки
Чтобы удалить ограничение проверки, используйте следующий SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.
Оператор ALTER TABLE
Можно выделить следующие уровни проверочных ограничений:
- уровень атрибута (столбца),
- уровень кортежа (строки),
- уровень отношения (таблицы).
В ограничении уровня столбца проверяется значение только одного отдельного столбца, другими словами, в ограничении данного типа имеется ссылка только на один столбец той таблицы, в определении которой содержится данное ограничение. Чтобы привести пример такого ограничения, вернёмся к схеме «Компьютерная фирма». В таблице Product в столбце type может находиться одно из трех значений. Мы можем запретить ввод любой другой информации в этот столбец при помощи такого ограничения:
Давайте сделаем отступление, чтобы познакомиться с оператором ALTER TABLE , который позволит нам изменять структуру таблицы, не пересоздавая её всякий раз заново. Это тем более важно, что изменение структуры может потребоваться тогда, когда таблица уже содержит данные.
С помощью оператора ALTER TABLE можно добавить или удалить столбцы, значения по умолчанию, а также ограничения.
В настоящий момент нас интересует добавление ограничения на столбец type , поэтому вначале приведём синтаксис оператора для добавления ограничения:
Давайте теперь добавим наше ограничение и проверим, как оно работает.
Чтобы убедиться в том, что ограничение работает как мы того ожидаем, попробуем добавить модель нового типа:
Как и ожидалось, в ответ мы получим сообщение об ошибке:
The INSERT statement conflicted with the CHECK constraint «chk_type». The conflict occurred in database «learn», table «dbo.product», column ‘type’. The statement has been terminated.
(Конфликт инструкции INSERT с ограничением CHECK «chk_type». Конфликт произошел в базе данных «learn», таблица «dbo.product», столбец ‘type’. Выполнение данной инструкции было прервано.)
Как легко догадаться, ограничение уровня строки содержит ссылки на несколько столбцов. При этом ограничение проверяется для каждой изменяемой строки отдельно. Строка может быть добавлена (или изменена), если ограничение не нарушено.
В качестве примера давайте запретим производителю Z выпускать что-либо помимо принтеров.
Итак, ограничение проверяет, что модель в таблице Product должна быть принтером производителя Z (maker=’Z’ and type= ‘printer’) или любого другого производителя (но не Z).
Если мы попытаемся добавить модель ПК производителя Z,
то получим приведенное выше сообщение об ошибке, в котором вместо имени ограничения chk_type будет упомянуто ограничение chk_maker_Z. При этом модель принтера будет добавлена без проблем:
Разумеется, другой производитель сможет выпускать все, что угодно: