Предложение 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. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.
ALTER TABLE — изменение таблицы в SQL
Рассказываем о команде ALTER TABLE и учим вносить с ее помощью изменения в таблицы и столбцы.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
ALTER TABLE — один из самых незаменимых инструментов в работе с базами данных SQL. В этой статье мы рассмотрим SQL оператор ALTER TABLE и его применение. Узнаем, как добавить или удалить поля с помощью этого инструмента, и рассмотрим различные примеры его использования. В данной статье мы не будем рассматривать MS SQL и остановимся на синтаксисе наиболее популярной версии — MySQL.
Синтаксис оператора ALTER TABLE в SQL
Синтаксис оператора ALTER TABLE выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
Из этой записи мы видим, что сценариев применения данной команды достаточно много. Давайте рассмотрим их. В качестве примера мы будем использовать базу данных slcbookshelf, которую мы создавали в статье о первичных и внешних SQL ключах.
use slcbookshelf; Добавление столбца в таблицу (ADD COLUMN)
Сейчас наша таблица выглядит следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Давайте добавим в нашу таблицу новый столбец, в котором будет отображаться автор каждой книги:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL; Данным запросом мы создали в нашей таблице новый столбец authors с типом NVARCHAR и длиной в 50 символов, который не может принимать пустое значение. Если мы не знаем автора произведения, тогда наша команда будет иметь такой вид:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL DEFAULT 'Неизвестен'; Теперь для существующих данных, для которых не заполнен столбец author, значение по умолчанию будет «Неизвестен».
Переименование столбца и таблицы
Переименование столбца (RENAME)
С помощью ALTER TABLE можно переименовать существующий столбец. Для этого выполните команду:
ALTER TABLE books RENAME COLUMN author TO authors; Переименование таблицы (RENAME)
При помощи ALTER TABLE можно переименовать таблицу. Выполняем запрос:
ALTER TABLE books RENAME TO books_selectel; Удаление столбца (DROP)
Чтобы удалить столбец из таблицы с помощью ALTER TABLE, требуется выполнить следующий запрос:
ALTER TABLE books DROP COLUMN authors; Изменение столбца (ALTER COLUMN)
Иногда бывают случаи, когда необходимо изменить уже созданный ранее столбец. Это действие можно выполнить с помощью команды ALTER TABLE. Для изменения существующего столбца необходимо выполнить такой запрос:
ALTER TABLE books ALTER COLUMN book_category VARCHAR(200); В данном примере мы изменили максимальное количество символов, которое может использоваться в полях столбца book_category с 255 до 200.
Также с помощью ALTER TABLE можно сделать действие сразу с несколькими столбцами. Чтобы изменить сразу несколько столбцов, вам потребуется использовать эту команду:
ALTER TABLE books MODIFY book_category VARCHAR(200), MODIFY book_name VARCHAR(200), . ; Таким запросом мы изменили сразу два столбца: book_category и book_name.
Изменение типа столбца
При помощи ALTER TABLE можно изменить тип столбца в таблице SQL. Изменение типа существующего столбца осуществляется при помощи команды:
ALTER TABLE books ALTER COLUMN book_category NVARCHAR(200); Выполнив эту команду, мы изменили тип book_category на NVARCHAR(200).
Добавление первичного и внешнего ключей при помощи ALTER TABLE
Вы можете определить существующий столбец в таблице в качестве первичного ключа с помощью команды ALTER TABLE. Запрос, добавляющий в таблицу первичный ключ, будет выглядеть следующим образом:
ALTER TABLE books ADD PRIMARY KEY (book_id); Аналогично при помощи ALTER TABLE можно добавить внешний ключ таблицы. Чтобы создать внешний ключ для таблицы MySQL выполните команду:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); В результате выполнения этой команды поле author_id в таблице books будет внешним ключом для аналогичного поля в таблице authors.
Работа с ограничениями
Ограничения — специальные правила, которые применяются к таблице, чтобы ограничить типы данных в таблице. Ограничения очень важны, так как их правильное применение помогает обеспечить целостность данных в таблицах и наладить стабильную работу базы. Давайте рассмотрим одно из таких ограничений — ограничение CHECK. Применяя ограничения CHECK к столбцу таблицы, мы создаем правило, по которому при добавлении данных СУБД будет автоматически проверять их на соответствии заданным правилам.
Создание ограничения
Например, если нам необходимо, чтобы все клиенты в базе данных Customers имели возраст больше 21 года, мы можем установить следующее ограничение:
ALTER TABLE Customers ADD CHECK (Age > 21); При применении такого ограничения стоит учитывать, что если в столбце уже существуют данные, не соответствующие ограничению, то команда будет выполнена с ошибкой. Чтобы избежать подобного поведения, вы можете добавить ограничение со значением WITH NOCHECK. Таким образом, текущие значения столбца не вызовут ошибок при выполнении запроса при несоответствии ограничению:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21); Добавление ограничений с именами
Так как ограничений в таблицах может быть много, добавление имен к ограничениям может в значительной мере упростить будущую работу с таблицей. Создать имя для ограничения можно при помощи оператора CONSTRAINT:
ALTER TABLE Customers ADD CONSTRAINT Check_Age_Greater_Than_Twenty_One CHECK (Age > 21); Удаление ограничений
ALTER TABLE можно пользоваться и для удаления ограничений. Для удаления существующих ограничений необходимо выполнить следующую команду:
ALTER TABLE Customers DROP Check_Age_Greater_Than_Twenty_One; После выполнения этой команды ограничение перестанет применяться при добавлении новых данных в столбец.
Заключение
В данной статье мы с вами узнали что такое ALTER TABLE, рассмотрели работу с этой командой и научились вносить с ее помощью изменения в таблицы и столбцы, а также рассмотрели несколько примеров использования данной команды, которые сильно упростят будущую работу с базами данных.
Сброс пароля root в MySQL
CHECK CONSTRAINT в MS SQL — Грабли по которым мы прошлись
Данная статья будет про то, как одна дружная команда веб разработчиков, не имея в своём составе опытного SQL разработчика, добавила Check Constraint в таблицу и прошлась по нескольким простым, но не сразу очевидным граблям. Будут разобраны особенности синтаксиса T-SQL, а также нюансы работы ограничений (СONSTRAINT’ов), не зная которые, можно потратить не мало времени на попытки понять, почему что-то работает не так. Так же будет затронута особенность работы SSDT, а именно как генерируется миграционный скрипт, при необходимости добавить или изменить ограничения (CONSTRAINT’ы).
Дабы читатель поскорей понял, стоит читать статью или нет, я сначала рассмотрю абстрактную задачу, по ходу решения которой будут заданы вопросы «А почему так?». Если вы сразу будете знать ответ, то смело бросайте чтение и переходите к следующей статье.
Разработаем гарем?
- гости в гареме запрещены и, соответственно, в базе не хранятся, т. е. хранятся только «хозяева» и их жёны
- у жён и их «хозяина» фамилия совпадает
- по фамилии можно уникально идентифицировать каждый гарем, т. е. одна и та же фамилия в разных гаремах встретиться не может.
В последний момент, приходит озарение, что на уровне схемы базы мы не гарантируем существование только одного мужчины в гареме. Решаем это исправить путём добавления проверочного ограничения (check constraint):
основанного на скалярной пользовательской функции (scalar-valued Function):
«А почему так?» №1.
При попытке вставить абсолютно валидные данные (как женщин, так и мужчин), понимаем, что мы всё поломали. Insert валится со следующей ошибкой:
«А почему так?» №2.
После того как побороли проблему вставки данных, решаем задеплоиться в QA окружение. Создаём миграционный скрипт при помощи SSDT, быстро просматриваем его. Находим какую-то не очень понятную строку (выделена красной рамкой).
Из комментария в инструкции PRINT кажется, что это запуск проверки ограничения на уже существующих строках. Но при создании ограничения мы же указали, что существующие строки проверять не нужно («Check Existing Data On Creation Or Re-Enabling» был установлен в “No”). Поэтому начинаем гуглить и находим «полезный» пост. Прочитав ответ и все комментарии к нему, обретаем глубокую уверенность, что эта инструкция включает проверку при вставке новых строк, а не валидирует существующие, т. е. нам обязательно нужно оставить эту строку, иначе ограничение вообще никогда не будет проверяться.
С гордостью за проделанную работу, отправляем скрипт, ждёмс… Спустя Х часов приходит отчёт, что наш миграционный скрипт успешно провалился. Смотрим отчёт.
Понимаем, что именно подозрительная инструкция провалила миграцию.
«А почему так?» №1 – Объяснение.
Тут всё предельно просто. Мы забыли, что проверка условий CHECK CONSTRAINT’а происходит уже после вставки строки в таблицу и в момент вставки первого мужчины в гарем, правильным условием будет равенство единице, а не нулю. В итоге функция была переписана на много проще.
Вычисляемые колонки (Computed column)
В выражении ограничения можно использовать вычисляемые колонки, но только если они физически сохраняются, т.е. у них свойство IsPersited установлено в Yes. На этапе выполнения проверки, все вычисляемые колонки будут иметь правильные значения и если вы обновите значения, от которых зависит вычисляемое значение, то в выражении CHECK CONSTRAINT’а будут переданы уже пересчитанные значения.
Оправдание
Дабы хоть как-то оправдать такую невнимательность скажу, что в нашем случае условия проверки ограничения были на много более витиеватые, и далеко не все были связаны с количеством строк в обновляемой таблице. А данные вставлялись в таблицу только в результате запуска сложного скрипта, разобраться с которым было крайне непросто.
«А почему так?» №2 – Объяснение.
Тут всё оказалось не столь прозрачно. Сначала пришлось всё-таки разобраться в истинном назначении упавшей инструкции. И, к превеликому нашему удивлению, мы поняли, что она делает именно то, что сказано в комментарии, а не то, что описано в найденном «полезном» посте (разбор синтаксиса будет ниже).
Узнав это, было логично предположить, что при создании миграционного скрипта была выбрана база, в которой на CK_Persons значение «Check Existing Data On Creation Or Re-Enabling» было “Yes”, а не “No”. Но эта теория провалилась. Меняя это значение и генерируя новый скрипт, стало понятно, что SSDT, вообще игнорируют это значение. Начали грешить на наличие бага в SSDT.
Очередной этап поисков навёл нас на следующий пост, из которого мы уже поняли, что это «фича, а не баг».
Согласно дизайна SSDT, при создании скрипта всегда создаётся ограничение, которое включено, т.е. проверяется для всех будущих INSERT/UPDATE. За это отвечает первая инструкция ALTER в нашем миграционном скрипте.
Вторая же инструкция ALTER (выделена красной рамкой) отвечает за валидацию существующих данных и является опциональной. За то будет ли эта инструкция добавлена в миграционный скрип, отвечает специальная опция генерации скрипта:
Включив её, мы для каждого нового миграционного скрипта активируем валидацию существующих данных, т.е. в него будет вставлена опциональная инструкция (второй ALTER). Иначе, инструкция попросту отсутствует и на существующих данных проверка не выполняется. Как это не прискорбно получается, но SSDT генерирует миграционный скрипт по принципу всё или ничего. Можно либо для всех вновь добавляемых ограничений включить проверку на существующих данных, либо для всех её пропустить. Для более тонкой настройки поведения придётся править скрипт вручную.
Ограничения (Constraints) в MS SQL
- NULL / NOT NULL ограничение – задаётся на уровне какого-то столбца и определяет, может ли хранится значение NULL в колонке.
- UNIQUE ограничение – позволяет обеспечить уникальность значений в одном или нескольких столбцах.
- PRIMARY KEY ограничение – практически тоже самое, что и UNIQUE ограничение, но в отличие от него, PRIMARY KEY не позволяет хранить NULL.
- CHECK ограничение – позволяет задать некое логическое условие, которое должно быть истинным (TRUE) при вставке или обновлении данных в таблице. Может быть задано как на уровне одного столбца, так и на уровне таблицы.
- FOREIGN KEY ограничение – позволяет обеспечить ссылочную связность двух таблиц. При вставке значения в колонку (или колонки) с FOREIGN KEY ограничением, будет производится проверка на наличие такого же значения в таблице, на которую указывает FOREIGN KEY. Если значения нет, то обновление или вставка строки завершается с ошибкой. Исключением может быть только значение NULL, если на колонке не задано ограничение NOT NULL. Кроме того, ссылаться можно только на колонку с уникальными значениями, т.е. с UNIQUE или PRIMARY KEY ограничением. Так же можно задать поведение, на случай обновления или удаления строки, в «отцовской» таблице:
- NO ACTION – отцовскую таблицу запрещено менять
- CASCADE – подчинённые строки будут обновлены или удалены, в зависимости от выполняемого действием над отцовской таблицей
- SET NULL – значение в подчинённой таблице будет установлено в NULL
- SET DEFAULT — значение в подчинённой таблице будет установлено в значение по умолчанию.
- Expression – любое допустимое T-SQL выражение в котором можно ссылаться на значения в проверяемой строке по имени столбцов
- Name – имя, уникально идентифицирующее ограничение в пределах базы данных
- Check Existing Data On Creation Or Re-Enabling – если ограничение создаётся на уже существующей таблице, то это значение “No” позволяет не пропустить валидацию существующих строк; в виду того, что существующую проверку можно временно выключить, то данное свойство так же определяет будет ли проводиться валидация имеющихся строк при включении ограничения.
- Enforce For INSERTs And UPDATEs – включает (Yes) или выключает (No) ограничение
- Enforce For Replication – позволяет пропустить проверку при вставке или обновлении строк агентом репликации
Примеры использования sys.check_constraints
DECLARE @name NVARCHAR(128) = 'CK_Persons' SELECT CASE [is_not_trusted] WHEN 1 THEN 'No' ELSE 'Yes' END AS [Check Existing Data], CASE [is_disabled] WHEN 1 THEN 'No' ELSE 'Yes' END AS [Enabled], CASE [is_not_for_replication] WHEN 1 THEN 'NO' ELSE 'YES' END AS [Enforce For Replication] FROM [sys].[check_constraints] WHERE name = @nameМожно получить ответ в более привычном формате, воспользовавшись оператором UNPIVOT:
DECLARE @name NVARCHAR(128) = 'CK_Persons' SELECT [Properties], [Values] FROM (SELECT CAST([definition] AS VARCHAR(MAX)) AS [Expression], CAST(CASE [is_not_trusted] WHEN 1 THEN 'No' ELSE 'Yes' END AS VARCHAR(MAX)) AS [Check Existing Data On Creation Or Re-Enabling], CAST(CASE [is_disabled] WHEN 1 THEN 'No' ELSE 'Yes' END AS VARCHAR(MAX)) AS [Enforce For INSERTs And UPDATEs], CAST(CASE [is_not_for_replication] WHEN 1 THEN 'NO' ELSE 'YES' END AS VARCHAR(MAX)) AS [Enforce For Replication], CAST([create_date] AS VARCHAR(MAX)) as [Created], CAST([modify_date] AS VARCHAR(MAX)) as [Modified] FROM [sys].[check_constraints] WHERE name = @name) p UNPIVOT ( [Values] FOR [Properties] IN ( [Expression], [Check Existing Data On Creation Or Re-Enabling] , [Enforce For INSERTs And UPDATEs], [Enforce For Replication], [Created], [Modified] ) ) AS unpvt;Особенности работы CHECK CONSTRAINT:
- Срабатывает только при INSERT и UPDATE операциях, при выполнении DELETE условие не проверяется
- Если проверочное условие равно NULL, то считается, что CHECK CONSTRAINT не нарушен
Синтаксис CHECK CONSTRAINT
У проверочного ограничения есть ряд свойств, которые необходимо задать при создании. Некоторые из них можно задать только при создании, а некоторые доступны для изменения, только на уже созданном ограничении. В таблице ниже отображены эти особенности.
“…ADD CONSTRAINT. ”
(создание)“ALTER…CONSTRAINT…”
(изменение)Name + — Expression + — Check Existing Data On Creation Or Re-Enabling + + Enforce For INSERTs And UPDATEs — + Enforce For Replication + — Добавление нового CHECK CONSTRAINT
Основы синтаксиса шаблонов T-SQL
- В квадратных скобках «[ ]» – указываются опциональные конструкции и могут быть опущены из конечного выражения
- В фигурных скобках «< >» — указывается список возможных конструкций, из которых необходимо выбрать одну
- Вертикальная черта «|» — отделяет элементы в фигурных скобках, среди которых необходимо выбрать единственный элемент
- [ WITH < CHECK | NOCHECK >] – в случае отсутствия применяется значение WITH CHECK
- [ NOT FOR REPLICATION ] – если конструкция указана, то ограничение не проверяется при вставке или обновлении данных в момент репликации; если конструкция пропущена –ограничение проверяется.
Примечание: шаблон приведён для случая создания ограничения на существующей таблице. Также можно создать ограничение в момент создания таблицы, тогда команда будет начинаться со слова CREATE и до слова WITH будет идти описание колонок таблицы.
Таблица для примеров
Примеры команд будут приведены для простейшей таблицы Employees, которая выглядит следующим образом:
Изменение существующего CHECK CONSTRAINT
- Check Existing Data On Creation Or Re-Enabling
- Enforce For INSERTs And UPDATEs
- [ WITH < CHECK | NOCHECK >] – в случае отсутствия применяется значение WITH NOCHECK
- [, …n] – позволяет задать имя более чем одного ограничения, к которым будут применены изменения; использование слова ALL изменения применятся ко всем проверочным ограничениям на таблице
Недокументированное поведение
Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте msdn.
Что бы это увидеть, необходимо рассмотреть все возможные комбинации состояний в сочетании со всеми возможными вариантами команд. Тогда будет видно, что в 5-ти случаях получаемое значение свойства «Check Existing Data» не соответствует ожиданиям.Состояние до выполнения команды T-SQL команда Состояние после выполнения команды Check Existing Data Enforce For INSERTs And UPDATEs Check Existing Data Enforce For INSERTs And UPDATEs No No NOCHECK No No No Yes NOCHECK No No Yes Yes NOCHECK No No No No CHECK No Yes No Yes CHECK No Yes Yes Yes CHECK Yes* Yes No No WITH NOCHECK NOCHECK No No No Yes WITH NOCHECK NOCHECK No No Yes Yes WITH NOCHECK NOCHECK No No No No WITH NOCHECK CHECK No Yes No Yes WITH NOCHECK CHECK No Yes Yes Yes WITH NOCHECK CHECK Yes* Yes No No WITH CHECK NOCHECK No** No No Yes WITH CHECK NOCHECK No** No Yes Yes WITH CHECK NOCHECK No** No No No WITH CHECK CHECK Yes Yes No Yes WITH CHECK CHECK Yes Yes Yes Yes WITH CHECK CHECK Yes Yes (*) Значение свойства «Check Existing Data» может быть переведено из значения «Yes» в значение «No», только если текущее значение свойства «Enforce For INSERTs And UPDATEs» отличается от заданного в команде.
(**) «Check Existing Data» может быть «Yes», только если ограничение включено (Enforce For INSERTs And UPDATEs = “Yes”). Т. е. в команде WITH CHECK NOCHECK часть WITH CHECK будет проигнорирована и «Check Existing Data» не будет установлено в «Yes». Это так же объясняет почему в качестве начальных состояний есть только 3 варианта для каждой команды (а не 4).
Удаление существующего CHECK CONSTRAINT
Команда очень проста и не требует дополнительных объяснений. Ещё шаблон:
Заключение
Искренне надеюсь, что после прочтения данной статьи, вы не пройдётесь по граблям, набившие нам пару неприятных шишек. А так же вы сможете комфортно создавать и поддерживать миграционные скрипты, в которых есть логика по работе с CHECK CONSTRAINT. Удачи!
- Блог компании Инфопульс Украина
- SQL
- Microsoft SQL Server
Оператор 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. При этом модель принтера будет добавлена без проблем:
Разумеется, другой производитель сможет выпускать все, что угодно: