Esat Erkec

How to use SQL Check Constraints

October 15, 2021 by

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.

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.

SQL check constraint usage details

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.

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.

SQL Server check constraint error details

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.

Check constraints and NULL values

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.

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.

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.

Using SQL Server check constraint with a scalar-valued function

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.

How to disable check constraints

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

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

Insert data into check constraint without validation

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

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

Check constraint and is not trusted status

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.

How to check constraints improve query performance

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.

How to list all constraints in a table

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.

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

An execution plan of a query

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

Analyzing IO of a query

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

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

Constraint and execution plan

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.

Esat Erkec
Latest posts by Esat Erkec (see all)
168 Views