CHECK constraint in SQL

Introduction

A CHECK constraint helps make sure the data in a table follows simple rules. It stops wrong or unexpected data from being saved.

When you want to make sure a person's age is always 0 or more.

When you want to ensure a product's price is never negative.

When you want to allow only certain values in a column, like 'Male' or 'Female' for gender.

When you want to keep dates in a valid range, like a start date before an end date.

When you want to prevent invalid status values in an order, like only 'Pending', 'Shipped', or 'Cancelled'.

Syntax

SQL

CREATE TABLE table_name (
  column_name datatype CHECK (condition),
  ...
);

-- Or add CHECK constraint after table creation:
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

The condition inside CHECK must be something that returns true or false.

If the condition is false, the database will not allow the data to be saved.

Examples

This makes sure the Age is 18 or older.

SQL

CREATE TABLE Employees (
  ID INT,
  Age INT CHECK (Age >= 18)
);

This prevents negative prices.

SQL

CREATE TABLE Products (
  ProductID INT,
  Price DECIMAL CHECK (Price >= 0)
);

This limits the Status column to only three allowed values.

SQL

ALTER TABLE Orders
ADD CONSTRAINT chk_status CHECK (Status IN ('Pending', 'Shipped', 'Cancelled'));

Sample Program

The first insert works because 85 is between 0 and 100. The second insert fails because 105 is outside the allowed range.

SQL

CREATE TABLE Students (
  StudentID INT,
  Name VARCHAR(50),
  Grade INT CHECK (Grade BETWEEN 0 AND 100)
);

INSERT INTO Students (StudentID, Name, Grade) VALUES (1, 'Alice', 85);
INSERT INTO Students (StudentID, Name, Grade) VALUES (2, 'Bob', 105);

Important Notes

CHECK constraints help keep your data clean and reliable.

They are checked every time you insert or update data.

Not all database systems support complex CHECK constraints equally.

Summary

CHECK constraints enforce simple rules on data in a table.

They prevent invalid data from being saved.

You can add them when creating a table or later with ALTER TABLE.