T-SQL: CHECK Constraints

Article
1/17/2024

Introduction

Constraints are database objects for providing Data Integrity. They are rules which apply to restrict values in a table. CHECK is just a constraint type. It can limit the values that are inserted or updated in a table column. We can use check constraints to validate data or define a custom scope of values such as some foods, birds etc. They can be very handy when designing a database. We will be more familiar with them in this article. This article has two parts. The first part is Design and Implementation and focuses on how to create CHECK constraints. The second part is CHECK Constraints in Action in which we will focus more on the details of the CHECK constraint implementation using T-SQL language.

Design and Implementation

Consider a table which has a column storing quantity as its value. In this situation, we want to enforce a rule that quantity must be a positive number. We want to add this validation rule to our database by creating a CHECK constraint.

We already have a great Graphical User Interface (GUI) tool to create CHECK constraints. We will see how to create CHECK constraints using Database Diagrams and Table Designer in SQL Server Management Studio (SSMS). Also, we can create these objects using Visual Studio SQL Server Data Tools (SSDT). Moreover, we will see how to create them using T-SQL.

Create and modify CHECK constraints using the Database Diagram (SSMS)

This is the simplest way to create and manage CHECK constraints. We can use SQL Server Management Studio (SSMS) to connect to SQL Server Database Engine, opening the Object Explorer, expanding the Databases node, right click on the Database Diagram node and creating a new Database Diagram.

After add/create a table, we have two GUI options to create a CHECK constraint:

1. By right click on the table in database diagram and select CHECK Constraints like the below picture:

2. By select the table in the Database Diagram and click on “Manage Check Constraints” button on the Database Diagram toolbar, as illustrated in the following picture:

When “Check Constraints” window appears, we can use the add button to create a new check constraint. If we click on Add button, a new check constraint with the name "CK_Table1" will appear. If we click on the Close button in this step, we see an error message like below picture:

This message means that the “Expression” property cannot be empty. The “Expression” property is the condition for validating data in a CHECK constraint. For example, if we do not want to permit entering a negative quantity in the Qty column, we need to fill the “Expression” property with such a Boolean condition. As illustrated in next picture, we

filled the “Expression” property with the condition Qty > 0,
changed the Name with “CK_Table1_PositiveQty”,
and entered a note in the Description for documenting it.

Customize CHECK Constraints using GUI

We left three options that are highlighted in the next picture. These advanced options are very important in some situations. Now, we can see their meanings and also their practical usages.

Check Existing Data on Creation or Re-Enabling

The first option is “Check Existing Data on Creation or Re-Enabling”. We usually select Yes for this option, except for two situations:

When we want to change a condition, but we do not want to apply it to the old data. For example, we have a column "Code" which had to be at least 6 characters in the past. Now rule changes, and the Code column value must have at least 8 characters. We cannot change old codes, because they are used vastly. So, we can set this option to No to feel free that the old data will be safe.
When we have a table that has huge data. In this situation, if we want to force new rule in the old data, we have to spend a lot of time to modify old data. We can set this option to No and apply our changes in “Expression” property, for now, later we will set it to Yes when we have enough time to fix the all the old invalid data.

Enforce for Inserts and Updates

The second option is “Enforce for Inserts and Updates”. This option name is probably a bit strange. But it simply means “Enable/Disable CHECK constraint”. So, we usually select Yes for this option. There are situations that we need to make our CHECK constraint disable. For example, when we want to import data from external data sources like excel. We can load all data, then clean them (update or remove those values that violate the conditions), and finally, enable it again. Therefore, if we want to disable our CHECK constraint, the simplest way is to select No for this option.

Enforce for Replication

The third option is “Enforce for Replication”. This is a precautionary option when using SQL Server Replication. We usually can leave it as Yes selected. But in some rare situations, it’s better to select No. For example, when our CHECK constraint will be violated and break the insert statement on identity columns which will cause the column identity value to increase without inserting data.

** **

Create and Modify CHECK Constraints using Table Designer (SSMS)

Using the Table Designer for managing CHECK constraints is as simple as using the Database Diagrams.

We can use SQL Server Management Studio (SSMS) to connect to SQL Server Database Engine
expanding the Object Explorer node
then expanding the Databases node
again, expanding the Tables nodes, and finding the desired table,
then expanding that specific table,
right click on the Constraints node and creating a new constraint.
- If we have a CHECK constraint and just want to modify it, we can simply double click on its name.

After following the above steps, the Check Constraints will pop up. Other steps to create or modify CHECK constraints are like the steps that we saw in the Create and modify CHECK constraints using Database Diagram (SSMS) section.

Create and Modify CHECK Constraints using Visual Studio SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) provides great tools to develop Database Projects. We can use Visual Studio to create a database project or simply connect to SQL Server database engine using SQL Server Object Explorer in Visual Studio, then right click on the database and select Create New Project, as shown in the following picture:

This way we can start creating a Database Project from our existing database. After creating a database project we can use the solution explorer to add or modify CHECK constraints. We can use these steps:

expanding database project
expanding dbo folder
then expanding Tables
double click on the specific table which we want to add or modify its CHECK constraints.
we can right click on Check Constraints and use Add New Check Constraint.

We can see the final step in the next picture:

By default, new CHECK constraint condition expression will be (1 = 1), and if we leave it as is, it will be created by this true condition. Therefore, we have to change it. Moreover, we have less GUI tools to change CHECK constraint options. By the way, we still have a T-SQL pane in which we can do everything as we need using code. Next picture shows a screenshot that illustrates these points:

Create and Modify CHECK Constraints using T-SQL

It is the time to move one step forward and using T-SQL to create and modify CHECK constraints. Using T-SQL we have a power that is more than all GUI tools. GUI tools are useful for rapid development, admin immediate tasks and developing small software applications. By writing directly T-SQL, whether, in SSMS or SSDT, we can have the most control over our software applications.

To start this part, we need to create a sample database with a sample table. We can do this by the following code:

use master 
go
-- drop database if it exists
if db_id('CheckCostraints') is  not null
    drop database  CheckCostraints;
go
-- create database
create database  CheckCostraints;
go
 
use CheckCostraints
go
--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int
    );

This code will create a sample database which has a table Table1. Similar to the former sample, this table has a column Qty to store quantity values that must be greater than zero.

To create a CHECK constraint using T-SQL language, we have two situations. One is Creating check constraint when creating table. Other is Creating check constraint when table exists.

Creating CHECK Constraint when creating table

Method One

If we wanted to create the CHECK constraints when creating the table, we could use the following code:

--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int  check ( Qty > 0 )
    )

We can put a CHECK constraint definition within the column definition. If we want to set a name for this CHECK constraints, we must use the CONSTRAINT keyword within the CHECK definition like this code:

--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int
         constraint ck_Table1_PositiveQty check ( Qty > 0 )
    )

This method has a limit. We cannot use any objects except that column in CHECK’s expression. For example, if we execute the following code, we get an error like the next picture:

--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int
         constraint ck_Table1_PositiveQty check ( Qty > Id )
    )

This error tells us that we cannot use other columns in the CHECK’s definition. The reason is that this method of creating CHECK constraints is limited to the same column scope. If we want to use other columns in the CHECK’s expression, we can use the next method.

Method Two

In this method, we still define the CHECK constraint within the table creation statement. But we put its definition at the end of the all columns definitions. We must separate the CHECK definition and the last column with a comma. Using this way, we can use other columns in the CHECK’s expression. The following code shows this:

--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int
    ,check ( Qty > Id )
    )

If we wanted to name this CHECK constraint, we could add the CONSTRAINT keyword at the beginning of the CHECK’s definition like the following code:

--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int
    ,constraint ck_Table1_PositiveQty check ( Qty > Id )
    )

Creating CHECK Constraint when table exists

There are many reasons to ignore adding CHECK constraints when creating the table. The lack of analyses, insufficient knowledge about the business rules, changing the business requirements, moving more business rules implementations to the database side to support multiple user interfaces, and even changing the software itself rules such as a manual code format or changing the database design, all are some of the reasons that lead us to add CHECK constraints when the table exists. And if the table exists we have to use ALTER TABLE syntax to add CHECK constraint like this code:

--create table "Table1"
create table  Table1
    (
     Id int  primary key
    ,Qty int
    )
go
 
-- create constrant without specify its name
alter table  dbo.Table1
add check  ( Qty > 0 );

If we wanted to add a named CHECK, we had to add the CONSTRAINT keyword. This syntax simply means that we want to ALTER the table and add a CONSTRAINT with a certain name which will check certain condition. So, this syntax is very smooth. We can change the above code like this one:

-- create constrant without specify its name
alter table  dbo.Table1
add constraint  ck_Table1_PositiveQty check ( Qty > Id )

Customize CHECK Constraint using T-SQL

When we wanted to create CHECK constraints using GUI tools, in the Customize CHECK Constraints using GUI section, we saw that there were three options to customize our CHECK constraints. By default, they were selected by Yes option. If we wanted to change them, we could select No for those options. When creating the CHECKs using T-SQL, the SQL Server behavior is the same. So, if we do not want to change them to No, there is no need to write more code. But if we want to change one of them we have an equivalent syntax with No selected options.

WITH NOCHECK

This option is like when we select No for the option Check Existing Data on Creation or Re-Enabling using SSMS GUI. If we do not want to enforce new condition for the existing data, we use this option. The following code shows the sample:

--with nocheck
alter table  dbo.Table1 with  nocheck 
add constraint  ck_Table1_PositiveQty 
    check (Qty>0)
go

Disable CHECK Constraint

To disable a CHECK constraint, we can use the NOCHECK CONSTRAINT. This option is like choosing No for the option Enforce for Inserts and Updates using SSMS GUI. Next code shows this:

--Disable constraint 
alter table  dbo.Table1
nocheck constraint  ck_Table1_PositiveQty
go

To disable all constraints on a table, we can use NOCHECK CONSTRAINT ALL like the following code:

--Disable all constraints
alter table  dbo.Table1
nocheck constraint  all
go

NOT FOR REPLICATION

This option is like when choosing No for the option Enforce for Replication using SSMS GUI. We can see how to do this using T-SQL in the following sample code.

--not for replication
alter table  dbo.Table1 with  nocheck 
add constraint  ck_Table1_PositiveQty 
    check not for replication (Qty>0)
go

CHECK Constraints in Action

We can see the new details and tips about the CHECK constraints implementation in this part. Also, we will see their limitations. To start this part, we can see the behavior of the CHECK constraints when dealing with NULL values.

Dealing with NULL

T-SQL is a declarative language that uses a three-valued logic. It means that we have three logical states; True, False and Unknown(NULL). CHECK constraints are validators to apply validation business rules. We usually think about a validation as “we have a condition that must be True”. But it is not exactly as the database engine thinks about it. In the CHECK constraint scope, the database engine changes the above sentence to this one:

“We have a condition that must be True or NULL”

This is a bit confusing. But the CHECK constraint logic is based on the following sentence:

“CHECK constraint fails when its condition is False”

So, if the whole CHECK constraint expression result will be NULL, the CHECK constraint will not avoid inserting the data.

Problem

Assuming that we have a Book table that has two date columns one for writing date and other for PublishDate. We want to apply a date validator to avoid inserting the writing dates that are greater than PublishDate in each row using the next code:

--create table
create table  dbo.Book
    (
     BookId int  primary key
    ,WritingDate date
    ,publishDate date
    );
 
--create check constraint
alter table  dbo.Book
add constraint  DateValidator check  ( WritingDate < publishDate );
 
 
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 1, null, '2014-10-10' );
 
select * 
from dbo.Book ;

This behavior becomes so important when we are thinking about the NULL values and the result of the expression that involves a NULL value. For example, the result of the all these conditions will be evaluated as NULL:

NULL + 10
15 > 10 AND NULL
15 < 20 OR NULL

In the former sample, assuming that we have these business rules:

The writing date and publish date can be NULL at the same time
The writing date must be less than the publish date

We can change the CHECK constraint expression like the below code. But it still does not avoid inserting unexpected values. This sample shows a common mistake when developers write CHECK constraints without considering this behavior.

-- delete existing data
truncate table  dbo.Book
go
--drop check constraint
alter table  dbo.Book
drop constraint  DateValidator 
go
 
--create check constraint
alter table  dbo.Book
add constraint  DateValidator 
check
    (
      WritingDate < publishDate
      or (
           WritingDate is  null
           and publishDate is null
         )
    ) ;
 
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 1, null, '2014-10-10' );
 
select * 
from dbo.Book

As illustrated in the above picture, this CHECK constraint also did not avoid inserting a NULL value for one of the two date columns in the Book table. So, what is the solution?

Solution One

To solve this problem, we have to go back to the sentence that the CHECK constraint logic is based on it. The sentence was this:

“CHECK constraint fails when its condition is False”

So, if we want to solve this problem in the CHECK constraint concept, we have to write an expression that checks these conditions instead of an older one:

If writing date is NULL, then the writing date must be replaced with a value to change the condition result to be False
If the publish date is NULL, then the PublishDate has to be replaced with a value to change the result to be False
If both date columns are NULL, then the condition result must be True

To pass the first condition, we can replace the writing date with a very big date to always be greater than the publish date. In the similar process for the second one, we can replace publish date with the minimum date value to break the rule and make it False. The last condition was implemented well in the former CHECK constraint and we use it as is. This code shows the solution:

-- delete existing data
truncate table  dbo.Book
go
--drop check constraint
alter table  dbo.Book
drop constraint  DateValidator 
go
--create check constraint
alter table  dbo.Book
add constraint  DateValidator 
check
    (
      isnull(WritingDate, '3000-1-1') <  isnull(publishDate,  '1-1-1')
      or (
           WritingDate is  null
           and publishDate is null
         )
    );
go
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 1, null, '2014-10-10' );
go
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 2, '2014-10-10',  null );
 
go
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 3, null, null );
 
go
 
select * 
from dbo.Book

As illustrated in the above picture, the first and the second insert statements failed by CHECK constraint but the third one executed successfully. This CHECK constraint meets all the rules that were desired. With this solution, we could decrease our results from three-valued to two-valued by removing NULL from the alternative results. So, we back to the Boolean algebra logic that the result must be True or False.

Solution Two

In the CHECK constraint expression, we can also use Scalar User Defined Functions. Therefore, we want to create one scalar function that will do all the business rule conditions in itself. Then we can manage its output result to be True or False. Next code shows this solution:

create function  dbo.fncDateValidator
    (
     @WritingDate date
    ,@publishDate date
    )
returns bit
as
    begin
        declare @Result bit;
        if @WritingDate is  null
            and @publishDate is not null
            set @Result = 0;
 
        if @WritingDate is  not null
            and @publishDate is null
            set @Result = 0;
 
        if @WritingDate is  null
            and @publishDate is null
            set @Result = 1;
 
        if @WritingDate is  not null
            and @publishDate is not null
            begin
                if @WritingDate < @publishDate
                    set @Result = 1;
                else
                    set @Result = 0;
            end
        return @Result;
    end
go
-- delete existing data
truncate table  dbo.Book
go
--drop check constraint
alter table  dbo.Book
drop constraint  DateValidator 
go
--create check constraint
alter table  dbo.Book
add constraint  DateValidator 
check
    (
      dbo.fncDateValidator(WritingDate,publishDate) = 1
    );
go
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 1, null, '2014-10-10' );
go
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 2, '2014-10-10',  null );
 
go
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 3, null, null );
 
go
 
select * 
from dbo.Book

Schema Binding Issue

Problem

The second solution in the previous sample will be handy when the rules are so complex. But using the scalar functions within CHECK constraints expression have few downsides. One of the most important problems comes when we want to drop or modify the scalar function. Assuming that we have to change the former sample rules. It must be as simple as changing the scalar function like the below code.

alter function  dbo.fncDateValidator
    (
     @WritingDate date
    ,@publishDate date
    )
returns bit
as
    begin
        declare @Result bit;
 
        if @WritingDate is  not null
            and @publishDate is not null
            begin
                if @WritingDate < @publishDate
                    set @Result = 1;
                else
                    set @Result = 0;
            end
        return @Result;
    end
go

As the above picture shows the problem, we cannot change this function. Moreover, we also cannot remove it.

Solution

This behavior is a great feature because it saves the Database Integrity. But it increases the steps to change the business rules inside the scalar function. First, we have to drop the CHECK constraint. Then we can apply the function changes. Finally, we have to add the CHECK constraint again. We can do this using the following code:

--drop check constraint
alter table  dbo.Book
drop constraint  DateValidator 
go
 
--modify scalar function
alter function  dbo.fncDateValidator
    (
     @WritingDate date
    ,@publishDate date
    )
returns bit
as
    begin
        declare @Result bit;
 
        if @WritingDate is  not null
            and @publishDate is not null
            begin
                if @WritingDate < @publishDate
                    set @Result = 1;
                else
                    set @Result = 0;
            end
        return @Result;
    end
go
 
--create check constraint again
alter table  dbo.Book
add constraint  DateValidator 
check
    (
      dbo.fncDateValidator(WritingDate,publishDate) = 1
    );
go

Sub-Query in CHECK Constraints

Problem

Using sub-queries is not allowed within the CHECK constraint expression in T-SQL language. We saw that we can use scalar user-defined functions in the check constraints expression, in the former samples. Although we can put the sub-query within the scalar function and use it within the CHECK’s expression, this is usually considered as a bad practice. This is because of two reasons:

Performance
CHECK constraints limitation

Performance

The performance downside is simply because of the nature of scalar functions that are processed per row. This is done when the scalar function uses a query. This can impact on performance when inserting data, blocking or even deadlocks especially in environments which has many users and huge tables. You can find more information about this problem by visiting SQL Server Scalar User-Defined Function Performance.

CHECK Constraint Limitation

In the beginning of this article, I said that “CHECK constraints can limit the entrance values that inserted or updated in a table column”. So, we cannot enforce a CHECK constraint when removing values. This limitation is based on the CHECK constraints of natural behavior, their role is limiting and validating the existing data. But by using sub-queries in the CHECK constraint using a scalar function workaround, we can break this natural behavior. There are many samples that show this limitation. To see one sample, please visit this BOL link.

Solution

The main solution to resolve using sub-queries within the CHECK constraints is to use sub-queries within the CHECK constraint without scalar function workaround. There are other database integrity tools such as using foreign keys, triggers or unique filtered indexes. Also, we can consider changing our database designs to avoid using this such bad practice. This sample is a great one that shows how we can avoid such bad practice.

Conflicting CHECK constraints

Problem

Assuming that we have two CHECK constraints on the former sample. One of them enforces that the writing date is greater than the publish date. Another check that two date columns must have the same values. Now, we want to insert data into the book table. The following code shows this:

-- delete existing data
truncate table  dbo.Book
go
--drop check constraint
alter table  dbo.Book
drop constraint  DateValidator 
go
 
--create first check constraint
alter table  dbo.Book
add constraint  DateValidator_1
check
    ( WritingDate > publishDate ) ;
 
--create second check constraint
alter table  dbo.Book
add constraint  DateValidator_2
check
    ( WritingDate = publishDate ) ;
 
 
--insert data with null vlues
insert  dbo.Book
        ( BookId, WritingDate, publishDate )
values
        ( 1, '1900-01-01',  '1900-01-01' );

The above picture shows that the termination error when inserting data. But both CHECK constraints added successfully first, and then we get the error when inserting the data into the table. It means that we can figure out that there is an issue with our database when using it.

Solution

If we have more than one CHECK constraints on a table, database engine does not verify whether their expressions have conflict. There is not any automatic verification of them. And, there is not any evaluation order when enforcing them. The solution is that we have to manually ensure that there is no conflict between them.

CHECK Constraints Metadata

We can use one of these two views to get information about each CHECK constraint in the database:

The first system view has a lot of details about the CHECK constraints. The second view has fewer columns but it is a bit more descriptive one than the first view.

Conclusion

We started with GUI tools and step by step went to the T-SQL side. Then we saw that the power and the weakness of the CHECK constraints. There are a few things about the CHECK constraints that we did not cover this article, such as error handling, the comparison between check constraints and rules or triggers etc. After the first revision of this article, Mr. Ronen Ariely (aka pituach) informed me about an strange BUG regarding using User Defined Functions in CHECK constraints. You can find more information about it in his comments.

Share via

T-SQL: CHECK Constraints

Introduction

Design and Implementation

Create and modify CHECK constraints using the Database Diagram (SSMS)

Customize CHECK Constraints using GUI

Check Existing Data on Creation or Re-Enabling

Enforce for Inserts and Updates

Enforce for Replication

Create and Modify CHECK Constraints using Table Designer (SSMS)

Create and Modify CHECK Constraints using Visual Studio SQL Server Data Tools (SSDT)

Create and Modify CHECK Constraints using T-SQL

Creating CHECK Constraint when creating table

Method One

Method Two

Creating CHECK Constraint when table exists

Customize CHECK Constraint using T-SQL

WITH NOCHECK

Disable CHECK Constraint

NOT FOR REPLICATION

CHECK Constraints in Action

Dealing with NULL

Problem

Solution One

Solution Two

Schema Binding Issue

Problem

Solution

Sub-Query in CHECK Constraints

Problem

Performance

CHECK Constraint Limitation

Solution

Conflicting CHECK constraints

Problem

Solution

CHECK Constraints Metadata

Conclusion

See Also

Additional resources

Share via

T-SQL: CHECK Constraints

Introduction

Design and Implementation

Create and modify CHECK constraints using the Database Diagram (SSMS)

Customize CHECK Constraints using GUI

Check Existing Data on Creation or Re-Enabling

Enforce for Inserts and Updates

Enforce for Replication

Create and Modify CHECK Constraints using Table Designer (SSMS)

Create and Modify CHECK Constraints using Visual Studio SQL Server Data Tools (SSDT)

Create and Modify CHECK Constraints using T-SQL

**Creating CHECK Constraint when creating table **

Method One

Method Two

Creating CHECK Constraint when table exists

Customize CHECK Constraint using T-SQL

WITH NOCHECK

Disable CHECK Constraint

NOT FOR REPLICATION

CHECK Constraints in Action

Dealing with NULL

Problem

Solution One

Solution Two

Schema Binding Issue

Problem

Solution

Sub-Query in CHECK Constraints

Problem

Performance

CHECK Constraint Limitation

Solution

Conflicting CHECK constraints

Problem

Solution

CHECK Constraints Metadata

Conclusion

See Also

Additional resources

Creating CHECK Constraint when creating table