SQL CHECK Constraint

This tutorial explains how to use the SQL CHECK constraint to limit the values allowed in a column.

Creating a CHECK Constraint

Using CHECK with CREATE TABLE

You can add a CHECK constraint when you create a table. This ensures that data inserted into the specified column meets the defined condition.

Example: Restricting Age to 18 or older

-- MySQL
CREATE TABLE Persons (
  Age INT,
  CHECK (Age >= 18)
);

-- SQL Server, Oracle, MS Access
CREATE TABLE Persons (
  Age INT CHECK (Age >= 18)
);
      

Naming Constraints and Multiple Columns

For better organization and to apply constraints to multiple columns, you can name your constraint and specify multiple conditions:

-- MySQL, SQL Server, Oracle, MS Access
CREATE TABLE Persons (
  Age INT,
  City VARCHAR(255),
  CONSTRAINT CHK_Person CHECK (Age >= 18 AND City = 'Sandnes')
);
      

Adding a CHECK Constraint After Table Creation

Using CHECK with ALTER TABLE

If your table already exists, you can add a CHECK constraint using ALTER TABLE:

-- MySQL, SQL Server, Oracle, MS Access
ALTER TABLE Persons
ADD CHECK (Age >= 18);


-- MySQL, SQL Server, Oracle, MS Access (Named Constraint, Multiple Columns)
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age >= 18 AND City = 'Sandnes');
      

Dropping a CHECK Constraint

To remove a CHECK constraint:

-- SQL Server, Oracle, MS Access
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

-- MySQL
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;