Check Constraints in SQL Server and First Order Logic

I have to give credit where credit is due, this post is inspired largely by a chapter in C.J. Date‘s book, “SQL and Relational Theory: How to Write Accurate SQL Code“. I would recommend the book to any database professional. Domain integrity and its enforcement is a crucial component of good database design. One of the tools SQL Server includes to help enforce domain integrity is check constraints. Check constraints help play an important role in database design because they can extend domain enforcement beyond mere primitives. This is particularly important because effective database design incorporates the enforcement of business rules into its design. For example, let’s say that you work for a bike company named (*cough*) Adventure Works. Your business has a business rule that product weight must be greater than zero. This is a fair thing because, at least in the world we operate in, weight isn’t a negative thing. In the AdventureWorks2012 database, turns out there’s example of such a thing in the Production.Product table.


If we look at the check constraint, we can see a very basic definition.


That’s great and all but what if businesses decided it wanted to something more complex? What if Adventure Works decided that they won’t allow bike blades to more than five pounds? I actually don’t know if this is true. In fact, I’ve always wanted to be a little bit more handy with my bike. We run a quick SELECT * against the Production.Product database and we can see that the blade has a ProductID of 316.


At first blush, this may seem like an IF THEN statement would do just the trick. That, if we were to modify the CHECK constraint for product weight that we may wish to have something along the lines of, IF ProductID = 316 then Weight > 0.00 AND Weight <= 5.00.

When we look at the documentation for CHECK constraints, there doesn’t seem to be a way to incorporate IF THEN logic into a CHECK constraints. Perhaps then a user defined function or UDF might seem to be in order. A potential UDF CHECK constraint might look like the following:


We add the constraint to the table (I dropped the previous check constraint for pedagogical purposes):


If we perform a simple unrestricted update such as the following:


We get the following error message:

Msg 547, Level 16, State 0, Line 3

The UPDATE statement conflicted with the CHECK constraint “CK_PRODUCT_WEIGHT”. The conflict occurred in database “AdventureWorks2012”, table “Production.Product”.

The statement has been terminated. Let’s see what happens when we do something simple like perform an unrestricted update:


The outcome looks like this:

SQL Server parse and compile time:

CPU time = 2 ms, elapsed time = 2 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table ‘Product’. Scan count 1, logical reads 1012, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:


CPU time = 15 ms, elapsed time = 110 ms.

(504 row(s) affected)

UDFs do not have a very good reputation. And using them in CHECK constraints can lead to performance issues and unexpected results as well.

Perhaps there is a better way. Think back to that freshman level Introduction to Logic course you took so long ago. There was a special identity for IF THEN statements. It was IF P THEN Q = NOT P OR Q. This simple logical equivalency can come in handy in a lot of places, especially where there isn’t support of IF THEN statements but there is for simple AND/OR operations. Here’s the truth table:

P NOT P Q IF P THEN Q NOT P OR Q
T F T T T
T F F F F
F T T T T
F T F T T

Instead of looking to a UDF to perform domain integrity, we can use a simple logical disjunction instead. So, instead the CHECK constraint might read:


The same unrestricted query looks like this:

SQL Server parse and compile time:

CPU time = 15 ms, elapsed time = 49 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table ‘Product’. Scan count 1, logical reads 1012, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:


CPU time = 16 ms, elapsed time = 13 ms.

(504 row(s) affected)

113 ms compared to 13 ms?!? The difference between the CHECK constraint using an UDF and one using a simple NOT P OR Q is separated by almost a factor of 10. This is a simple table with a tiny amount of data, 504 rows. Imagine what this looks like at scale with millions of rows. A little bit of logic can save a lot of time. Moral of the story? Where and when you can, use logic and relational algebra, it will usually yield better database performance.

I know I linked to it in this blog post already but I want to call it out again, please read Gail Shaw’s post on UDFs. It’s very good: http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/.

Oh, and I think a bike blade might be a type of fender. At least, that’s what my searches on REI.com tell me.