SSDT database projects: how to write check constraints
I came across an interesting quirk of database projects a few days ago. More specifically, a quirk when you come to publish a project that includes check constraints.
I had a few tables with check constraints added to different columns to specify that only particular values could be used in those columns. It looked something like the following:
CREATE TABLE [CheckConstraintExamples].Customer
(
[CustomerID] INT NOT NULL,
[Forename] VARCHAR(50) NOT NULL,
[Surname] VARCHAR(50) NOT NULL,
[Email] VARCHAR(200) NOT NULL,
[CustomerType] VARCHAR(20) NOT NULL,
[CustomerLoyalty] VARCHAR(20) NOT NULL,
[CustomerValue] VARCHAR(20) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerID]),
CONSTRAINT [CK_Customer_CustomerType] CHECK
(CustomerType IN (
'Basic'
, 'Premium'
, 'Platinum')),
CONSTRAINT [CK_Customer_CustomerLoyalty] CHECK
(CustomerLoyalty IN (
'Low'
, 'Medium'
, 'High'
, 'Lapsed')),
CONSTRAINT [CK_Customer_CustomerValue] CHECK
(CustomerValue IN (
'Low'
, 'Medium'
, 'High'
, 'Superspender'
, 'None'))
);
That all looks ok, and everything published fine, and kept on publishing fine until I happened to check the generated script for some of the publishes (I was in the early stages of development at this point so deployments were just being done through Visual Studio direct to my machine).
When I did I noticed that every deployment I was getting code like this:
GO
PRINT
N'Dropping [CheckConstraintExamples].[CK_Customer_CustomerLoyalty]...';
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
DROP CONSTRAINT [CK_Customer_CustomerLoyalty];
GO
PRINT
N'Dropping [CheckConstraintExamples].[CK_Customer_CustomerType]...';
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
DROP CONSTRAINT [CK_Customer_CustomerType];
GO
PRINT
N'Dropping [CheckConstraintExamples].[CK_Customer_CustomerValue]...';
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
DROP CONSTRAINT [CK_Customer_CustomerValue];
GO
PRINT
N'Creating [CheckConstraintExamples].[CK_Customer_CustomerLoyalty]...';
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH NOCHECK
ADD CONSTRAINT [CK_Customer_CustomerLoyalty]
CHECK (CustomerLoyalty IN (
'Low'
, 'Medium'
, 'High'
, 'Lapsed'));
GO
PRINT
N'Creating [CheckConstraintExamples].[CK_Customer_CustomerType]...';
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH NOCHECK
ADD CONSTRAINT [CK_Customer_CustomerType]
CHECK (CustomerType IN (
'Basic'
, 'Premium'
, 'Platinum'));
GO
PRINT
N'Creating [CheckConstraintExamples].[CK_Customer_CustomerValue]...';
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH NOCHECK
ADD CONSTRAINT [CK_Customer_CustomerValue]
CHECK (CustomerValue IN (
'Low'
, 'Medium'
, 'High'
, 'Superspender'
, 'None'));
GO
PRINT
N'Checking existing data against newly created constraints';
GO
USE [$(DatabaseName)];
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK
CHECK CONSTRAINT [CK_Customer_CustomerLoyalty];
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK
CHECK CONSTRAINT [CK_Customer_CustomerType];
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK
CHECK CONSTRAINT [CK_Customer_CustomerValue];
That’s dropping all my constraints, recreating them using WITH NOCHECK, and then using WITH CHECK to check the existing data. Obviously all the data will pass those checks, but that’s going to add some time to my deployments, and as the size of the data and the number of checks both increase, these checks will take more and more time.
So, what’s going on? The answer was pretty simple really, when you create a table with check constraints like the ones I’ve used, SQL Server doesn’t use the IN when generating the table definition. Instead it breaks that out into a series of OR predicates, and when you deploy again the deployment process doesn’t recognise that the OR predicates are the same as the single IN predicate. Because it thinks the constraints are different, it drops the existing one and creates a new one based on the code in your project. Then the SQL Server engine transforms the IN predicate in the new constraint into a series of OR predicates and the cycle begins again.
The only solution is to re-write the CHECK CONSTRAINTs to use OR instead of IN, like this:
CREATE TABLE [CheckConstraintExamples].Customer
(
[CustomerID] INT NOT NULL,
[Forename] VARCHAR(50) NOT NULL,
[Surname] VARCHAR(50) NOT NULL,
[Email] VARCHAR(200) NOT NULL,
[CustomerType] VARCHAR(20) NOT NULL,
[CustomerLoyalty] VARCHAR(20) NOT NULL,
[CustomerValue] VARCHAR(20) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerID]),
CONSTRAINT [CK_Customer_CustomerType] CHECK
(
CustomerType ='Basic'
OR CustomerType = 'Premium'
OR CustomerType = 'Platinum'
),
CONSTRAINT [CK_Customer_CustomerLoyalty] CHECK
(
CustomerLoyalty = 'Low'
OR CustomerLoyalty = 'Medium'
OR CustomerLoyalty = 'High'
OR CustomerLoyalty = 'Lapsed'
),
CONSTRAINT [CK_Customer_CustomerValue] CHECK
(
CustomerValue = 'Low'
OR CustomerValue = 'Medium'
OR CustomerValue = 'High'
OR CustomerValue = 'Superspender'
OR CustomerValue = 'None'
)
)
Once I’d done that I thought everything would be fine so I generated another deployment script. Turns out there was one more little thing. When the SQL Server engine generated the object definition for those checks, it ordered the different predicates according to where they were in the original IN predicate, from last to first, which gives definitions like this:
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK ADD
CONSTRAINT [CK_Customer_CustomerLoyalty] CHECK
((
[CustomerLoyalty]='Lapsed'
OR [CustomerLoyalty]='High'
OR [CustomerLoyalty]='Medium'
OR [CustomerLoyalty]='Low'
))
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK ADD
CONSTRAINT [CK_Customer_CustomerType] CHECK
((
[CustomerType]='Platinum'
OR [CustomerType]='Premium'
OR [CustomerType]='Basic'
))
GO
ALTER TABLE [CheckConstraintExamples].[Customer]
WITH CHECK ADD
CONSTRAINT [CK_Customer_CustomerValue] CHECK
((
[CustomerValue]='None'
OR [CustomerValue]='Superspender'
OR [CustomerValue]='High'
OR [CustomerValue]='Medium'
OR [CustomerValue]='Low'
))
GO
Whereas if you check my definitions in the previous code block, I’d defined them the other way around. I suspected this meant the deployment engine still thought they were different check constraints, and to test it I deployed my revised project and generated another script. Sure enough, this final script wasn’t dropping and recreating those check constraints any more, and when I regenerated the table definition in management studio, it showed all of the predicates in the order I specified.
I said above that the only way to fix the issue I could see is to re-write the check constraints using a list of OR predicates. That’s not quite true. The other thing you can do is write your constraints like that to begin with, because you’re aware of the issue, and hopefully this blog means a few more of you will do that and not have to go through the re-write task I now have ahead of me.
Chris Johnson 
Trackbacks & Pingbacks