What is a CHECK Constraint?

In database terms, a CHECK constraint is a type of constraint that checks data before it enters the database.

CHECK constraints help maintain data integrity, because they prevent invalid data entering the database.

When a table has a CHECK constraint enabled, data can only enter that table if it doesn’t violate the CHECK constraint. Data that violates the CHECK constraint cannot enter that table.

When you create a CHECK constraint, you provide a logical expression that is used to check the data. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated. If the result is false, then a constraint violation has occurred, and the DBMS will typically raise an error.

CHECK Constraints vs Foreign Key Constraints

CHECK constraints are similar to foreign key constraints in that they both 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
CHECK constraints determine the valid values from a logical expression.

So it’s perfectly valid (and probably expected) that you’ll have both foreign keys and CHECK constraints on your tables, as well as any number of other constraints as required.

Column-Level vs Table-Level

CHECK constraints can be defined at the column level or the table level.

A column-level CHECK constraint applies to just the data in one column.
A table-level CHECK constraint applies to the whole row, and checks data from multiple columns.

Column-Level

For example, a column-level CHECK constraint might look like this:

CHECK (Price > 0)

This is a column-level constraint because it’s only checking one column (the Price column).

Table-Level

A table-level CHECK constraint might look like this:

CHECK (Price >= Discount)

This is a table-level constraint because it’s checking more than one column (the Price column and the Discount column).

Creating CHECK Constraints

CHECK constraints are typically created as part of the CREATE TABLE statement.

The actual syntax for creating a CHECK constraint might depend on the DBMS that you’re using.

Here’s an example of using SQLite to create a table with a CHECK constraint:

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName, 
    Price 
    CHECK (Price > 0)
);

In this case a column-level CHECK constraint was created.

Here’s an example of creating a CHECK constraint in SQL Server.

Adding CHECK Constraints to an Existing Table

Some DBMSs allow you to add CHECK constraints to an existing table via the ALTER TABLE statement.

For an example, see Add a CHECK Constraint to an Existing Table in SQL Server.

Other DBMSs (such as SQLite) don’t allow you to add a CHECK constraint to an existing table. In such cases, you’ll need to drop the table and create it again (along with the CHECK constraint). Any data will need to be transferred back in once you’ve created the CHECK constraint.

Enabling/Disabling CHECK Constraints

Some DBMSs provide functionality for enabling/disabling CHECK constraints. This feature could be handy for “special situations” where you need to insert data that is known to violate existing constraints, but you still need to insert it anyway.

In SQLite, you can use the ignore_check_constraints PRAGMA statement to enable/disable the enforcement of all CHECK constraints.

In SQL Server, you can enable/disable them individually:

In SQL Server you can also enable/disable all constraints at the database level:

SQL Server also let’s you enable/disable them at the table level:

Such functionality may also come with further related functionality, such as choosing whether or not to perform checks against existing data before enabling a constraint, and having a “trusted” flag to indicate whether or not existing data has been checked.

See What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server for a discussion on this.