Конфликт инструкции alter table с ограничением check

alter FUNCTION [Kuri].[fnGetAge](@kuri_cust_Id int,@amt decimal)
RETURNS SMALLINT
AS
    BEGIN
    DECLARE @isVallid  bit = 0
    declare @payed decimal(14,2)
    declare @totaltillnow decimal(14,2)
    select @payed = isnull(SUM(Payment.amt),0)  from Kuri.Payment where Payment.Kuri_Cust_ID = @kuri_Cust_id
    select @totaltillnow = isnull(SUM(NextLotAmount),0) from Kuri.Kuri_GivenDetails
    inner join Kuri.kuri_Customer  
    on Kuri_GivenDetails.kuri_Id  = kuri_Customer.kuri_ID 
     where kuri_Customer.kuri_Cust_id =  @kuri_Cust_id
     if((@payed + @amt) < @totaltillnow)
        set @isVallid = 1
        RETURN @isVallid
    END;
GO

ALTER TABLE [Kuri].[Payment]  WITH CHECK ADD  CONSTRAINT PaymentCheck CHECK (kuri.fnGetAge(kuri_Cust_ID,amt) >= 1 )
GO

error :

The ALTER TABLE statement conflicted with the CHECK constraint
«PaymentCheck». The conflict occurred in database «MERP», table
«Kuri.Payment».

Table structure is like this

CREATE TABLE [Kuri].[Payment](
    [payment_ID] [int] IDENTITY(1,1) NOT NULL,
    [payment_Date] [date] NOT NULL,
    [bill_No] [nvarchar](25) NOT NULL,
    [Kuri_Cust_ID] [int] NOT NULL,
    [vr_ID] [int] NOT NULL,
    [amt] [decimal](14, 2) NULL,
    [created_ID] [int] NULL,
    [created_Date] [datetime] NULL,
    [modified_ID] [int] NULL,
    [modified_Date] [datetime] NULL,
    [authorized_ID] [int] NULL,
    [authorized_Date] [datetime] NULL,
  CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED 
  ([payment_ID] ASC)

 ALTER TABLE [Kuri].[Payment]  WITH CHECK ADD  CONSTRAINT [FK_Payment_kuri_Customer] FOREIGN KEY([Kuri_Cust_ID])
 REFERENCES [Kuri].[kuri_Customer] ([Kuri_Cust_ID])

 ALTER TABLE [Kuri].[Payment] CHECK CONSTRAINT [FK_Payment_kuri_Customer]

Scenario:

You are working as SQL Server developer, you are asked to add Check Constraint to one existing table dbo.Employee on FName column and write logic for Check Constraint so it should always accept alphabets.

When you tried to add Check Constraint, you got below error.

Msg 547, Level 16, State 0, Line 19
The ALTER TABLE statement conflicted with the CHECK constraint «Chk_dbo_Employee_FName». 
The conflict occurred in database «YourDatabaseName», table «dbo.Employee», column ‘FName’.

Solution:

Let’s generate the scenario first for the error. Create sample dbo.Employee table with some sample data.

--Create Table  
use YourDatabaseName
go
Create table dbo.Employee
(
FName VARCHAR(100) Not Null,
LName VARCHAR(100),
StreetAddress VARCHAR(255)
)
--Insert data in sql table
insert into dbo.Employee(FName,LName,StreetAddress)
values ('Aamir','Shahzad','xyz address')
go
insert into dbo.Employee(FName,LName,StreetAddress)
values ('Raza A',Null,'abc address')
go

Now run the alter table statement to add Check Constraint.  Once you will execute this statement you will get above error. as existing data does not qualify for Check Constraint. We have space in first name for ‘Raza A’ and our Check Constraint says that the data in FName should be always alphabets.

Alter table dbo.Employee
Add Constraint Chk_dbo_Employee_FName
Check (FName not like '%[^a-z]%')

1) First Solution: Correct Existing Data

Fist solution can be, you find the data that does not qualify for Check Constraint and correct that and then add Check Constraint.

2) If business don’t want to fix the existing data and want to implement Check Constraint from moving forward, you can create the Check Constraint with Nocheck. By doing that it will not validate existing data against our Check Constraint rule but only apply to new data.

Alter table dbo.Employee with nocheck
Add Constraint Chk_dbo_Employee_FName
Check (FName not like '%[^a-z]%') 

Let’s insert couple of records and check if our Constraint is working as expected.

insert into dbo.Employee(FName,LName,StreetAddress)
values ('Test 123',Null,'test address')
go

insert into dbo.Employee(FName,LName,StreetAddress)
values ('Najaf',Null,'test address')
go

The first insert will fail as it does not qualify with our Check Constraint rule. Second record will be inserted successfully. Let’s check the data in table now.

How to add Check Constraint to Column with Existing Data in SQL Server

Video Demo : How to fix error the Alter table statement conflicted with the Check Constraint

Время на прочтение
9 мин

Количество просмотров 65K

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

«А почему так?» №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

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

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

Добавление нового CHECK CONSTRAINT

Опциональные секции:

1. [ WITH { CHECK | NOCHECK } ] – в случае отсутствия применяется значение WITH CHECK
2. [ NOT FOR REPLICATION ] – если конструкция указана, то ограничение не проверяется при вставке или обновлении данных в момент репликации; если конструкция пропущена –ограничение проверяется.

Примечание: шаблон приведён для случая создания ограничения на существующей таблице. Также можно создать ограничение в момент создания таблицы, тогда команда будет начинаться со слова CREATE и до слова WITH будет идти описание колонок таблицы.

Примеры:

Изменение существующего CHECK CONSTRAINT

Для обновления существующего проверочного ограничения используется конструкция ALTER TABLE. Для изменения доступны только следующие свойства:

• Check Existing Data On Creation Or Re-Enabling
• Enforce For INSERTs And UPDATEs

Опциональные секции:

1. [ WITH { CHECK | NOCHECK } ] – в случае отсутствия применяется значение WITH NOCHECK
2. [, …n] – позволяет задать имя более чем одного ограничения, к которым будут применены изменения; использование слова ALL изменения применятся ко всем проверочным ограничениям на таблице

Примечание 1: хоть имя и нельзя переименовать при помощи синтаксиса ALTER TABLE, это всё же возможно сделать, используя системную хранимую процедуру sp_rename.
Примечание 2: при необходимости изменить свойства «Expression» или «Enforce For Replication», необходимо сначала удалить существующее ограничение, а потом заново его создать с нужными значениями этих свойств.

Примеры:

Недокументированное поведение

Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте msdn.
Что бы это увидеть, необходимо рассмотреть все возможные комбинации состояний в сочетании со всеми возможными вариантами команд. Тогда будет видно, что в 5-ти случаях получаемое значение свойства «Check Existing Data» не соответствует ожиданиям.

(*) Значение свойства «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. Удачи!