How to use SQL Check Constraints

This article intends to give comprehensive information about the usage and other details of SQL check constraints.

What is the SQL check constraint

The check constraints are the rule or set of rules that help to check the inserted (or updated) data values to tables based on a certain condition. So that, we can validate the newly inserted data values based on a specified rule before accepting them to the table. The main advantage of check constraints is that they ensure that all data in a column contains validated values according to the check constraint rule.

For example, we define a population column for the CountryList table and we want it to hold only positive data.

CREATE TABLE CountryList (

Id INT IDENTITY PRIMARY KEY,

CountryName VARCHAR(255) NOT NULL,

CountryPopulation INT CHECK(CountryPopulation > 0)

)

As we can see the CHECK (CountryPopulation > 0) expression is involved in the table creation query. This expression specifies a SQL check constraint and checks whether the data inserted into the CountryPopulation table is greater than 0. If the inserted data is equal to or less than 0 the record cannot enter the table because it will violate the constraint rule.

Now, in the following insert query, we will try to insert a negative value into the CountryPopulation column.

1 2	INSERT INTO CountryList VALUES('PeaceCountry',-30)

As we can see, the check constraint does not allow to insert of new data that is not validated by the specified rule and returns an error.

SQL check constraint referring to multiple columns

We can define data validation rules for the check constraints that refer to multiple columns. In the following example, we want to ensure that the next census date must be greater than the last census date. To create this data validation rule, we will use the CHECK(LastCensus<NextCensus)) expression in the table creation query.

CREATE TABLE CountryListCensus (

Id INT IDENTITY PRIMARY KEY,

CountryName VARCHAR(255) NOT NULL,

CountryPopulation INT CHECK(CountryPopulation > 0),

LastCensus DATE,

NextCensus DATE,

CHECK(LastCensus<NextCensus)

)

Now we want to add a row to the CountryListCensus table, but with equal values of column LastCensus and column LastCensus. In this case, our query will return an error.

1 2	INSERT INTO CountryListCensus VALUES('PeaceCountry',10000, '20210101','20210101')

SQL check constraint and NULL values

The NULL values indicate an unknown value in SQL therefore this value is evaluated as UNKNOWN by the check constraints. For this reason, NULL values are often evaluated as true by check constraints.

INSERT INTO CountryList

VALUES( 'GreenCountry', NULL );

SELECT *

FROM CountryList;

Using the NOT NULL constraints is the best method to control a field to contain a NULL value or not.

SQL check constraint and user-defined functions

A scalar-valued user-defined function returns a single value after its invocation. We can use this type of function in the check constraints to define a data validation rule. At the same time, we can pass the inserted data value to this function as a parameter. For example, the function below calculates the year difference between 2 dates and we will use this function in the check constraint.

CREATE FUNCTION CalcTwoDateDiff

(@EndDate DATE,@BegDate DATE)

RETURNS INT

BEGIN

DECLARE @Result AS INT=0

SELECT @Result=DATEDIFF(YEAR,@BegDate,@EndDate)

RETURN @Result

END

At this time, we add a new check constraint to an existing table. In order to add a new constraint to an existing table, we need to give a constraint name.

ALTER TABLE CountryListCensus

ADD CONSTRAINT CheckDateDiff

CHECK (dbo.CalcTwoDateDiff(NextCensus,LastCensus)=4)

As we can see, the check constraint rule control that there must be 4 years between the censuses. The following insert statement will be executed successfully.

1 2	INSERT INTO CountryListCensus VALUES('PeaceCountry',10000, '20170101','20210101')

Disabling a check constraint

Under some circumstances, we may need to make an exception in the SQL check constraint rules and insert some data that does not validate the check constraints. To resolve this issue, we can reconsider the check constraint rule or temporarily disable the check constraint. To disable any check constraint, we must first find the name and the table it is defined in. The following query list the defined constraints for the CountryListCensus.

SELECT

name,

is_disabled,

is_not_trusted,

definition,

OBJECT_NAME(parent_object_id) AS TableName

FROM sys.check_constraints

WHERE OBJECT_NAME(parent_object_id)= 'CountryList'

As the second step, we disable the constraint that is related to countries’ populations through the following query.

1 2	ALTER TABLE CountryList NOCHECK CONSTRAINT CK__CountryLi__Count__62AFA012;

After that, we can insert negative values into the CountryList column.

INSERT INTO CountryList VALUES('PeaceCountry',-30)

SELECT * FROM CountryList

After disabling the constraint and then inserting the data into the table we can reenable the constraint with the help of the following query.

1 2	ALTER TABLE CountryList CHECK CONSTRAINT CK__CountryLi__Count__62AFA012;

When we recheck the status of the constraint the status will be shown enable but the trusted status will be shown disable.

SELECT

name,

is_disabled,

is_not_trusted,

definition,

OBJECT_NAME(parent_object_id) AS TableName

FROM sys.check_constraints

WHERE OBJECT_NAME(parent_object_id)= 'CountryList'

It means that the check constraint has not been verified by the system for all rows. SQL Server uses the check constraint to improve the performance of the query. Then the data engine will have to read the data from the table. Now we will look at how the check constraints help to improve query performance. In the Adventureworks database, we will execute the following query and analyze the execution plan.

1 2	SET STATISTICS IO ON SELECT FirstName,LastName FROM Person.Person WHERE EmailPromotion = 4

As we can see, the query optimizer returns a very simple query plan because it knows that the EmailPromotion column must only include a value between 0 and 2 because of the CK_Person_EMailPromotion constraint.

At the same time, there isn’t any physical or logical read is performed by this query. Now we will disable the CK_Person_EmailPromotion check constraint and then re-execute the same query.

1 2	ALTER TABLE Person.Person NOCHECK CONSTRAINT CK_Person_EmailPromotion;

After executing the same query with the same condition, we will see that the execution plan is completely different.

SET STATISTICS IO ON

SELECT FirstName,LastName FROM Person.Person WHERE

EmailPromotion = 4

Besides this execution plan changing, the query has performed 3821 logical reads.

Now, we will re-enable the CK_Person_EmailPromotion constraint and re-execute our sample query.

1 2	ALTER TABLE Person.Person CHECK CONSTRAINT CK_Person_EmailPromotion;

After re-enabling the constraint the execution plan of the query will not change, because the constraint is still in untrusted status.

SET STATISTICS IO ON

SELECT FirstName,LastName FROM Person.Person WHERE

EmailPromotion = 4

Conclusion

In this article, we have explored the SQL check constraints usage details. At the same time, check constraints can help to improve the performance of the queries.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec