SQL CHECK Constraint

Summary: in this tutorial, you will learn about the SQL CHECK constraint and how apply it to enforce domain integrity.

Introduction to SQL CHECK constraint

The CHECK constraint limits the values that you can enter into one or more columns in a table. The CHECK constraint enforces data integrity of the column automatically.

The CHECK constraint is used with logical expressions, which return TRUE or FALSE.

Let’s take a look at the batches table:

For example, in the batches table, we can add a CHECK constraint to make sure that the end date is always greater than or equal to the begin date and the begin date is greater than or equal 1970-01-01. The logical expression illustrates the logic is as follows:

enddate >= begindate AND begindate >= '1970-01-01'Code language: SQL (Structured Query Language) (sql)

If you try to enter a begin date that is less than or equal the end date or begin date is less than 1970-01-01, the database engine rejects the change and issues an error.

You can create multiple CHECK constraints for a column or a single CHECK constraint for multiple columns in a table.

A CHECK constraint is like a FOREIGN KEY constraint in terms of enforcing the values that you can enter in the column. The difference is the CHECK constraint uses a logical expression to determine valid values, while a FOREIGN KEY constraint uses values from a column in another table.

Creating CHECK constraint examples

We can define a CHECK constraint when creating a table as follows:

CREATE TABLE batches (
  batchid int(11) NOT NULL,
  batchname varchar(255) NOT NULL,
  begindate date NOT NULL,
  enddate date NOT NULL,
  courseid int(11) NOT NULL,
  PRIMARY KEY (batchid),
  CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES courses (courseid),
  CONSTRAINT chk_date CHECK(enddate >= begindate AND begindate >= '1970-01-01')
)Code language: SQL (Structured Query Language) (sql)

In the above query, we defined a CHECK constraint using the following clause:

CONSTRAINT chk_date CHECK(enddate >= begindate AND begindate >= '1970-01-01')Code language: SQL (Structured Query Language) (sql)

The name of the CHECK constraint is  chk_date and the logical expression to determine valid values for the begin date and end date column columns is

enddate >= begindate AND begindate >= '1970-01-01'Code language: SQL (Structured Query Language) (sql)

You can add a CHECK constraint to an existing table by using the ALTER TABLE as follows:

ALTER TABLE batches
ADD CONSTRAINT chk_date 
CHECK(enddate >= begindate AND begindate >= '1970-01-01')Code language: SQL (Structured Query Language) (sql)

Removing CHECK constraint

To remove a CHECK constraint, you also use the  ALTER TABLE statement. For example, the following statement removes the  chk_date CHECK constraint:

ALTER TABLE batches
DROP CONSTRAINT chk_dateCode language: SQL (Structured Query Language) (sql)

In this tutorial, we have shown you how to use the SQL CHECK constraint to enforce values that can be entered in a column or multiple columns of a table.