In SQL Server you can create a CHECK constraint in a table to specify the data values that are acceptable in one or more columns.

If a table has a CHECK constraint on it, and you try to provide data that doesn’t conform to the CHECK constraint, the operation will fail with an error.

This helps to maintain data integrity, because it helps to prevent invalid data from entering the database.

When you create a CHECK constraint, you provide a logical expression that returns TRUE or FALSE. This logical expression is what’s used to check the data.

CHECK constraints are similar to foreign key constraints because they control the values that are put in a column. However, the difference is in how they determine which values are valid: Foreign key constraints obtain the list of valid values from another table, while CHECK constraints determine the valid values from a logical expression.

Constraints can be defined at the column level or table level. A column-level constraint applies to just the data in that column. A table-level constraint applies to the whole row, and checks data from multiple columns.

Below are examples of creating both column-level and table-level CHECK constraints.

Example 1 – Create a Column-Level CHECK Constraint

Here’s an example of creating a basic column-level CHECK constraint at the time of creating a table.

CREATE TABLE ConstraintTest
(
  ConstraintTestId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Price smallmoney NOT NULL,
  CONSTRAINT chkPrice CHECK (Price > 0)
);

In this case, the CHECK constraint specifies that all data in the Price column must be greater than 0. In other words, the price can’t be zero and it can’t be negative. This is a column-level constraint because it applies to data in one column.

Because this is a column-level constraint, I could have defined it as part of the column (without the comma). So I could have done this:

CREATE TABLE ConstraintTest
(
  ConstraintTestId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Price smallmoney NOT NULL CONSTRAINT chkPrice CHECK (Price > 0)
);

Either way, let’s try to insert an invalid value:

INSERT INTO ConstraintTest ( Price )
VALUES ( 0 );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkPrice". The conflict occurred in database "Test", table "dbo.ConstraintTest", column 'Price'.

Example 2 – Add More Columns and Another Column-Level CHECK Constraint

Let’s add some more columns to our table and then add another column-level CHECK constraint.

ALTER TABLE ConstraintTest
ADD 
  TeamSize tinyint NOT NULL,
  StartDate date NOT NULL,
  EndDate date NOT NULL,
  CONSTRAINT chkTeamSize CHECK (TeamSize >= 3 AND TeamSize <= 15)
  ;

One of the new columns records the number of team members. In this case, the business rule is that a team must have at least 3 members, but no more than 15. Therefore the database should prevent the situation where a team has less than 3 members or more than 15.

Let’s try to insert an invalid value:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 1, 2, '2020-01-01', '1900-02-02' );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkTeamSize". The conflict occurred in database "Test", table "dbo.ConstraintTest", column 'TeamSize'.

Example 3 – Add a Table-Level CHECK Constraint

Now let’s add a table-level constraint. This will check data in two columns.

By the way, you don’t have to add another column in order to add a CHECK constraint. You can simply add the constraint by itself.

Example:

ALTER TABLE ConstraintTest
  ADD CONSTRAINT chkValidEndDate 
  CHECK (EndDate >= StartDate)
  ;

In this case I add a constraint to ensure that the end date can never be earlier than the start date. This is checking data across two columns and is therefore a table-level constraint.

Try to insert an invalid value:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 1, 3, '2020-01-01', '1900-02-02' );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkValidEndDate". The conflict occurred in database "Test", table "dbo.ConstraintTest".

Note that in order to test this constraint, I had to increase the team members to 3 to prevent the previous constraint from being triggered first (CHECK constraints are validated in the order they are created).

Example 4 – Alter a CHECK Constraint

You can’t actually alter a CHECK constraint. If you need to alter it, you’ll need to drop it and create it with the new definition.

Example:

ALTER TABLE ConstraintTest 
  DROP CONSTRAINT chkTeamSize;

ALTER TABLE ConstraintTest
  ADD CONSTRAINT chkTeamSize 
  CHECK (TeamSize >= 5 AND TeamSize <= 20)
  ;

As mentioned, CHECK constraints are validated in the order they are created, so this can affect which error is caught first.

Therefore in this case, if I try to insert an invalid value (and also include invalid dates), the invalid dates will be caught first:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 1, 4, '2020-01-01', '1900-02-02' );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkValidEndDate". The conflict occurred in database "Test", table "dbo.ConstraintTest".

So in order to check my latest constraint, I’ll need to fix the date issue first:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 1, 4, '2020-01-01', '2020-02-02' );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkTeamSize". The conflict occurred in database "Test", table "dbo.ConstraintTest", column 'TeamSize'.

So my latest constraint is working as expected.

Example 5 – CHECK Constraints and IDENTITY Columns

So now that we’ve tested the constraints, let’s go ahead and insert valid data:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 1, 5, '2020-01-01', '2020-02-02' );

Result:

+--------------------+---------+------------+-------------+------------+
| ConstraintTestId   | Price   | TeamSize   | StartDate   | EndDate    |
|--------------------+---------+------------+-------------+------------|
| 13                 | 1.0000  | 5          | 2020-01-01  | 2020-02-02 |
+--------------------+---------+------------+-------------+------------+

Finally we get a successful insert.

However, you’ll notice that the IDENTITY column has already incremented to 13.

Remember when I first created the table I defined the ConstraintTestId column to use IDENTITY(1,1), which means that it should start at 1 and automatically increment by 1 with each row insert.

But now that I’ve finally inserted my first row, the value is already 13. That’s because the IDENTITY column is incremented even when a CHECK constraint causes the INSERT operation to fail.

Note that I made a few extra failed inserts while coming up with the examples for this article, so the value has incremented to a higher value than the what you’ll get if you simply follow along step by step with this article.

In any case, let’s do one last failed insert, and then a successful one to confirm this.

Failed insert:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 2, 4, '2020-01-02', '2020-02-03' );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkTeamSize". The conflict occurred in database "Test", table "dbo.ConstraintTest", column 'TeamSize'.

Successful insert:

INSERT INTO ConstraintTest ( Price, TeamSize, StartDate, EndDate )
VALUES ( 2, 6, '2020-01-02', '2020-02-03' );

SELECT * FROM ConstraintTest;

Result:

+--------------------+---------+------------+-------------+------------+
| ConstraintTestId   | Price   | TeamSize   | StartDate   | EndDate    |
|--------------------+---------+------------+-------------+------------|
| 13                 | 1.0000  | 5          | 2020-01-01  | 2020-02-02 |
| 15                 | 2.0000  | 6          | 2020-01-02  | 2020-02-03 |
+--------------------+---------+------------+-------------+------------+

We can see that the IDENTITY column jumps from 13 to 15, so it obviously incremented during the failed insert.

Some Restrictions of CHECK Constraints

Here are a few restrictions to be mindful of when working with CHECK constraints:

• The search condition must evaluate to a Boolean expression and cannot reference another table.
• The expression cannot contain alias data types.
• CHECK constraints cannot be defined on text, ntext, or image columns.