SQL Server Check Constraint: Full Beginner-Friendly Tutorial

When working with SQL Server databases, one of the most important things we should always keep in mind is the integrity of our data. We should do our best to make sure the data in our database is valid. One of the best ways we can accomplish this is through the use of Check constraints.

A Check constraint is one of several constraints we can add to columns in our tables to basically make sure invalid data cannot be entered into our database.

We all know there can be times where a user might accidentally enter invalid data into our tables. For example, maybe they entered an Employees salary is -$32,000 per year, or maybe they marked an item as “Out of Stock” when there are dozens of the item in inventory!

When a database or software application is released into the wild, you will be shocked by some of the things a user will do! Things you never thought they could do.

In this tutorial, we’re going to break down the concept of the Check constraint in SQL Server. Through a Check constraint, we’ll be able reduce the chances of some wonky data being entered into our database.

Also make sure you download your FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

We’ll discuss the following topics in this tutorial about Check constraints:

1. What is a Check constraint?
2. Examples of Check constraints
3. The WITH NOCHECK option
4. Tips and tricks

And so it begins…

1. What is a Check constraint?

When a Check constraint is put on a column in a table, it defines a predicate that any new value must pass in order for that new value to be entered into the column.

A predicate is basically a condition that is placed on your data. A Check constraint will check the data before inserting it into the table to make sure it is valid according to the rules of the Check constraint.

There are plenty of times where we might want to check the data before entering it into the table. We might want to use a Check constraint when the data type we’re using isn’t restrictive enough, for example.

We’ll look at some examples soon!

2. Examples of Check constraints

There are a couple of different ways we can add a Check constraint to a column.

Let’s say we run a used car dealership, and we have a table called “VehicleInventory“. Here’s the definition of the table (before adding any constraints):

CREATE TABLE VehicleInventory
(
CarID INT PRIMARY KEY IDENTITY,
VIN VARCHAR(20),
CarYear INT,
Make VARCHAR(15),
Model VARCHAR(15),
Mileage INT
)

Maybe we want to introduce a Check constraint to restrict values in the CarYear column. Maybe we have a rule saying we won’t accept a trade-in that is older than, say, 1970. Because let’s face it, if we get a trade-in that is older than 1970, it’s likely a hunk of junk.

But of course, as the table is now, we can add a row with a CarYear value less than 1970. As I said earlier, there are a couple of different ways we can introduce a check constraint on the CarYear column. Let’s take a look at each.

Check constraint defined next to the column

In our CREATE TABLE statement, we can outline the Check constraint directly next to the column we want the Check constraint to be on (in our case, the CarYear column).

The syntax would look like this:

CREATE TABLE VehicleInventory
(
CarID INT PRIMARY KEY IDENTITY,
VIN VARCHAR(20),
CarYear INT CHECK(CarYear >= 1970),
Make VARCHAR(15),
Model VARCHAR(15),
Mileage INT
)

Notice the keyword “CHECK” next to the CarYear column. This tells SQL we want to add a Check constraint to the column.

Next to that, we have the predicate for the Check constraint. This is the test that any new or updated value must pass in order to be inserted into the table. In our case, we want to make sure the CarYear value is greater than or equal to 1970.

Pretty simple, right?

Check constraint defined after the column list

There are a couple of drawbacks to the previous method for adding a constraint to a column.

First, your predicate can only check one column value. That column is, of course, the column we are adding the constraint to (the CarYear column in our case).

Second, the constraint is given a random name by SQL Server. If we look in the object explorer for the constraint, we can see the name that SQL gave it:

That name isn’t very user friendly 🙁

Thankfully, there is another way to create a constraint that can resolve both of these problems.

First, we’ll talk about creating a Check constraint that validates more than one column value. For example, if we wanted to say all new rows need to have a CarYear value greater than or equal to 1970, and they must be an American car (let’s say Ford, Chevy, Dodge, Chrysler, or Jeep), we might try creating the constraint like this:

CREATE TABLE VehicleInventory
(
CarID INT PRIMARY KEY IDENTITY,
VIN VARCHAR(20),
CarYear INT CHECK((CarYear >= 1970) and (Make IN ('Ford', 'Chevy', 'Dodge', 'Chrysler', 'Jeep'))),
Make VARCHAR(15),
Model VARCHAR(15),
Mileage INT
)

But this will fail, giving us an error message:

The error says: Column CHECK constraint for column ‘CarYear’ references another column, table ‘VehicleInventory’.

Basically, if we define a Check constraint next to a column, the only column allowed in the predicate is that column.

But to make this work, we can define the Check constraint after the column list, like so:

CREATE TABLE VehicleInventory
(
CarID INT PRIMARY KEY IDENTITY,
VIN VARCHAR(20),
CarYear INT,
Make VARCHAR(15),
Model VARCHAR(15),
Mileage INT,
CONSTRAINT chk_YearAndMake CHECK((CarYear >= 1970) and (Make IN ('Ford', 'Chevy', 'Dodge', 'Chrysler', 'Jeep')))
)

The syntax is fairly straightforward:

1. Use the CONSTRAINT keyword, followed by the name we want to give our constraint.
2. Use the CHECK keyword to outline that we want a Check constraint, followed by the predicate.

This works fine. Also notice we can give the constraint a name when it’s created in this way. I chose to give it a name of “chk_YearAndMake“. This is much better than the name SQL Server gave it before.

Testing it out

Now let’s test out our constraint to make sure it works. Let’s first try adding a vehicle with a CarYear value that is less than 1970:

We get a proper error message saying “The INSERT statement conflicted with the CHECK constraint “chk_YearAndMake”“.

Ok, fine. Let’s make the CarYear greater than 1970, but change the Make to be ‘Subaru‘:

We still get an error message. Now we’re violating the rule that any new car must be an American car.

Finally, let’s make everything valid and see that the row get’s inserted successfully:

Nice.

(By the way, there’s no such thing as a 1999 Chevy Bel-Air. Chevy stopped making the Bel-Air’s in 1975 🙁 )

Check constraint created in an ALTER TABLE statement

So let’s say the table already exists, and you want to add a check constraint to it. In this case, you would create the constraint by using the ALTER TABLE statement.

To demonstrate, I want to show you a classic example of a Check constraint you would create for a Products table that has a Qty column and an InStock column:

CREATE TABLE Products
(
ProdID INT PRIMARY KEY IDENTITY,
ProductName VARCHAR(20),
Qty INT,
InStock BIT
)

Let’s think about the Qty and InStock columns. The InStock column uses the handy BIT data type which only allows 1 or 0 to basically represent True or False respectively.

Ideally, the InStock column would be set to ‘True‘ if the Qty is greater than 0, right?

Also, I would expect the InStock value to be ‘False‘ if the Qty is equal to 0.

It would be bad if we marked an item as out-of-stock when there were actually plenty of the item in inventory. It would also be bad if we said an item was in-stock but we didn’t actually have any of the item in inventory! I could see a lot of customer complaints about that.

Through the use of a Check constraint, we can disallow a user from entering invalid data like that.

We can create the Check constraint in an ALTER TABLE statement using the ADD CONSTRAINT keywords, like so:

ALTER TABLE Products 
ADD CONSTRAINT chk_reconcileInventory 
CHECK (NOT ((InStock = 0 AND Qty > 0) OR (InStock = 1 AND Qty = 0)))

The syntax is fairly straightforward:

1. Use the ALTER TABLE statement to outline the table we want to add the Check constraint to.
2. Use the ADD CONSTRAINT keywords, followed by the name we want to give our constraint.
3. Use the CHECK keyword to outline that we want a Check constraint, followed by the predicate

The logic in the predicate of this Check constraint is a bit of a thinker. We’re basically saying we want to NOT allow two scenarios:

1. The item is out of stock but the quantity is greater than zero.
2. The item is in stock but the quantity is zero.

Testing it out

Let’s run a couple of quick tests. First, let’s test with valid data:

Nice. Now let’s test when the item is out of stock but the quantity is greater than zero:

We get a proper error message. Now let’s test when an item is in stock, but it’s quantity is zero:

Again, a proper error message!

3. The WITH NOCHECK option

There is another problem that you might encounter if you want to add a Check constraint to a table. Let’s think about the scenario where the table already exists and is populated with data.

Let’s go back to our VehicleInventory table. I’m going to drop the table and re-create it. I’ll also populate it with some data:

--Drop table
DROP TABLE VehicleInventory

--Re-create table without constraint yet
CREATE TABLE VehicleInventory
(
CarID INT PRIMARY KEY IDENTITY,
VIN VARCHAR(20),
CarYear INT,
Make VARCHAR(15),
Model VARCHAR(15),
Mileage INT,
)

--Populate table with data
INSERT INTO VehicleInventory(VIN, CarYear, Make, Model, Mileage)
VALUES
('737JDJJ', 1967, 'Ford', 'Mustang', 135000),
('92JDJGG', 1998, 'Chevy', 'Silverado', 150000),
('232HSHF', 2002, 'Toyota', 'Corolla', 220000),
('52HFHND', 1969, 'Dodge', 'Charger', 95000)

Let’s check the data in the table:

So let’s say we wanted to create our same Check constraint after the fact. We want to create a constraint that disallows any vehicle that is older than 1970, or if the Make is not Ford, Chevy, Chrysler, Dodge, or Jeep.

We recognize that we already have data in the table that will violate this rule.

Regardless, let’s try to add the constraint:

When we try to create a Check constraint after the fact (that is, after data is already in the table), SQL Server will check if any of the existing rows in the table violate the new constraint. If so, it disallows you from creating the constraint.

This is the default behavior. Thankfully, there is a way to tell SQL Server to basically create the constraint anyway, even if there is existing invalid data in the table. It’s through the use of the handy WITH NOCHECK option. Check it out:

You put the WITH NOCHECK keywords directly after the table you want to add the constraint to.

The placement seems odd to me. Seems like it ought to be after the predicate of the Check constraint. But whatever.

Now the Check constraint exists, and it will validate data going forward. Let’s try to add another violating row now:

The Check constraint won’t let it happen!

4. Tips, tricks, and links

Here is a list of some helpful tips and tricks you should know about working with Check constraints.

Tip # 1: You can disable a Check constraint

Let’s think about that last example, where we created a Check constraint to prevent users from entering details for a car that is older than 1970 or if the Make is not American.

What if someone wanted to trade in a particularly nice 1956 Chevy Bel Air?

Normally we don’t take cars older than 1970, but in this case, we want to make an exception. This is a freaking nice car that we could re-sell for a huge profit $$$$.

But again, presently the Check constraint won’t allow us to enter details for this car. Thankfully, there is a way to disable the Check constraint. We use the handy NOCHECK keyword in an ALTER TABLE statement:

ALTER TABLE VehicleInventory
NOCHECK CONSTRAINT chk_VehicleDetails

That’s it! Notice we don’t need to outline the predicate of the constraint. We just need to outline the name of the constraint.

Now, we can enter data for our 1956 Chevy:

Superb!

Don’t forget to re-enable the Check constraint when you’re done:

--Enable constraint
ALTER TABLE VehicleInventory
CHECK CONSTRAINT chk_VehicleDetails

Notice when you enable the Check constraint, it sort-of looks like you’re just re-creating the constraint but without the predicate.

Tip # 2: Be careful about NULL values and how they are evaluated in your predicate

Let’s think about what happens when SQL Server is evaluating a value in the predicate of a check constraint.

The only way that a value will be rejected is if the value evaluates to False.

If the value evaluates to anything else, the value passes the test and is accepted.

You might be saying “What the heck does he mean ‘anything else‘. The only other thing it can evaluate to is True! A predicate is either True or it’s freaking False!”

Not quite. You need to know that SQL Server uses three-valued logic when evaluating a value in a predicate. This basically means that a predicate can evaluate to True, False, or Unknown. A predicate would evaluate to Unknown when NULL is involved.

Let’s think about an example using our last Check constraint. It requires that a new car be newer than 1970 and that it’s an American car. What do you think will happen if I try to insert the following row?:

INSERT INTO VehicleInventory(VIN, CarYear, Make, Model, Mileage)
VALUES
('373KDKJF', NULL, 'Ford', 'F150', 130000)

Notice the CarYear value is NULL. Maybe we don’t know the year of the car at this moment, but that’s ok. We just want to get it in the database.

SQL Server will evaluate the predicate using NULL as the value of CarYear. In other words, the predicate will look like this:

(NULL >= 1970) AND ('Ford' IN ('Ford', 'Chevy', 'Dodge', 'Chrysler', 'Jeep'))

So, is NULL greater than or equal to 1970? The answer is: Unknown.

And anytime we use the ‘AND‘ operator with Unknown, the result will be Unknown. Therefore, we can say when the CarYear value is NULL, this predicate evaluates to Unknown.

Since the predicate does not evaluate to False, the value is accepted and the row is inserted into the table:

Interesting, eh?

Read more about NULL here: SQL Server NULL: Are you making these 7 mistakes?

Tip # 3: Drop a constraint using the DROP CONSTRAINT statement

Dropping a constraint is simple. Just run the DROP CONSTRAINT statement in an ALTER TABLE statement, like this:

ALTER TABLE VehicleInventory DROP CONSTRAINT chk_VehicleDetails

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, make sure you download your FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!