SQL CHECK Constraint

Summary: in this tutorial, you will learn how to use the SQL CHECK constraint to validate data in a column or a set of columns based on a Boolean expression.

Introduction to SQL CHECK constraint

A CHECK constraint is an integrity constraint in SQL that allows you to specify that a value in a column or set of columns must satisfy a Boolean expression.

You can define a CHECK constraint on a single column or the whole table. If you define the CHECK constraint on a single column, the CHECK constraint checks the value for this column only. However, if you define a CHECK constraint on a table, it limits value in a column based on values in other columns of the same row.

The CHECK constraint consists of the keyword CHECK followed by a Boolean expression in parentheses:

CHECK(Boolean_expression)

To assign a CHECK constraint a name, you use the following syntax:

CONSTRAINT constraint_name CHECK(Boolean_expression)

It is important to note that the CHECK constraint is satisfied when the Boolean expression returns true or the NULL value. Most Boolean expressions evaluate NULL if one of the operands is NULL, they will not prevent null values in the constrained column. To make sure that the column does not contain the NULL values, you use the NOT NULL constraint.

SQL CHECK constraint examples

Let’s take some examples of creating CHECK constraints.

To create the products table with the value in the selling_price column positive, you use the following CREATE TABLE statement:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    selling_price NUMERIC(10,2) CHECK (selling_price > 0)
);

The CHECK constraint comes after the data type of the column. If you insert or update the selling price with a negative value, the expression selling_price >= 0 returns false and the RDMBS returns an error.

You can assign the CHECK constraint a separate name. The constraint name helps you clarify the error message returned by the RDBMS and know exactly which constraint the value has been violating.

To assign a name to a constraint, you use the keyword CONSTRAINT followed by the constraint’s name.

For example, the following statement assigns positive_selling_price the name of CHECK constraint on the selling_price column.

The syntax for assigning a CHECK constraint is as follows:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    selling_price NUMERIC(10,2) CONSTRAINT positive_selling_price CHECK (selling_price > 0)
);

You can define a CHECK constraint that refers to multiple columns. Suppose you store both selling prices and costs in the products table, and you want to ensure that the cost is always lower than the selling price.

CREATE TABLE products (
	product_id INT PRIMARY KEY,
	product_name VARCHAR (255) NOT NULL,
	selling_price NUMERIC (10, 2) CHECK (selling_price > 0),
	cost NUMERIC (10, 2) CHECK (cost > 0),
	CHECK (selling_price > cost)
);

First, you have the two CHECK constraints associated with the selling_price and cost columns to make sure that values in each column are positive.

Second, you have another CHECK constraint that is not attached to any column, instead, it appears as the last clause in the CREATE TABLE statement.

The first two constraints are column constraints, whereas the third one is a table constraint. The table constraint does not associate with any column.

You can assign the table constraint a name using the following syntax.

CREATE TABLE table_name (
   …,
   CONSTRAINT check_constraint_name CHECK (Boolean_expression)
);

For example, the following statement assigns a name to the CHECK constraint above.

CREATE TABLE products (
	product_id INT PRIMARY KEY,
	product_name VARCHAR (255) NOT NULL,
	selling_price NUMERIC (10, 2) CHECK (selling_price > 0),
	cost NUMERIC (10, 2) CHECK (cost > 0),
	CONSTRAINT valid_selling_price  CHECK (selling_price > cost)
);

In this tutorial, you have learned how to use the CHECK constraint to validate data based on a Boolean expression.