Данная статья будет про то, как одна дружная команда веб разработчиков, не имея в своём составе опытного SQL разработчика, добавила Check Constraint в таблицу и прошлась по нескольким простым, но не сразу очевидным граблям. Будут разобраны особенности синтаксиса T-SQL, а также нюансы работы ограничений (СONSTRAINT’ов), не зная которые, можно потратить не мало времени на попытки понять, почему что-то работает не так. Так же будет затронута особенность работы SSDT, а именно как генерируется миграционный скрипт, при необходимости добавить или изменить ограничения (CONSTRAINT’ы).
Дабы читатель поскорей понял, стоит читать статью или нет, я сначала рассмотрю абстрактную задачу, по ходу решения которой будут заданы вопросы «А почему так?». Если вы сразу будете знать ответ, то смело бросайте чтение и переходите к следующей статье.
Разработаем гарем?
«Гарем» — система, которая будет позволять вести учёт людей в «храме любви».
Для простоты разработки примем следующее:
- гости в гареме запрещены и, соответственно, в базе не хранятся, т. е. хранятся только «хозяева» и их жёны
- у жён и их «хозяина» фамилия совпадает
- по фамилии можно уникально идентифицировать каждый гарем, т. е. одна и та же фамилия в разных гаремах встретиться не может.
Для хранения людей создаётся таблица Persons:
В последний момент, приходит озарение, что на уровне схемы базы мы не гарантируем существование только одного мужчины в гареме. Решаем это исправить путём добавления проверочного ограничения (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
Как уже говорилось выше, в данной статье осталось разобраться с премудростями синтаксиса создания и обновления проверочных ограничений. Но прежде чем мы приступим, давайте для полноты картины вспомним немного общей информации об ограничениях в MS SQL Server.
Ограничения – механизм, который позволяет задавать набор правил, направленных на поддержание целостности данных. Правила могут быть заданы как на уровне колонки в таблице, так и на уровне всей таблицы.
MS SQL Server поддерживает следующие виды ограничений:
- 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 — значение в подчинённой таблице будет установлено в значение по умолчанию.
Теперь немного подробней о CHECK CONSTRAINT’ах. Рассмотрим ограничение, которое было упомянуто выше. Ниже представлено окно свойств этого ограничения в Management Studio:
Основными свойствами являются:
- Expression – любое допустимое T-SQL выражение в котором можно ссылаться на значения в проверяемой строке по имени столбцов
- Name – имя, уникально идентифицирующее ограничение в пределах базы данных
- Check Existing Data On Creation Or Re-Enabling – если ограничение создаётся на уже существующей таблице, то это значение “No” позволяет не пропустить валидацию существующих строк; в виду того, что существующую проверку можно временно выключить, то данное свойство так же определяет будет ли проводиться валидация имеющихся строк при включении ограничения.
- Enforce For INSERTs And UPDATEs – включает (Yes) или выключает (No) ограничение
- Enforce For Replication – позволяет пропустить проверку при вставке или обновлении строк агентом репликации
Вся эта информация доступна нам так же из системного представления (view) sys.check_constraints. Оно содержит по одной строке для каждого CHECK CONSTRAINT в базе данных. Мы его иногда используем в миграционных скриптах, когда нужно убедится в существовании или в отсутствии какого-либо ограничения.
Примеры использования sys.check_constraints
Sql
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:
Sql
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
Для обновления существующего проверочного ограничения используется конструкция ALTER TABLE. Для изменения доступны только следующие свойства:
- Check Existing Data On Creation Or Re-Enabling
- Enforce For INSERTs And UPDATEs
Опциональные секции:
- [ WITH { CHECK | NOCHECK } ] – в случае отсутствия применяется значение WITH NOCHECK
- [, …n] – позволяет задать имя более чем одного ограничения, к которым будут применены изменения; использование слова ALL изменения применятся ко всем проверочным ограничениям на таблице
Примечание 1: хоть имя и нельзя переименовать при помощи синтаксиса ALTER TABLE, это всё же возможно сделать, используя системную хранимую процедуру sp_rename.
Примечание 2: при необходимости изменить свойства «Expression» или «Enforce For Replication», необходимо сначала удалить существующее ограничение, а потом заново его создать с нужными значениями этих свойств.
Примеры:
Недокументированное поведение
Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте 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. Удачи!
