SQL Topics
CHECK
title: CHECK
The CHECK constraint ensures that every value in a column satisfies a specific condition before the database stores it.
Use it to enforce business rules such as minimum age, positive prices, valid statuses, or score ranges.
What a Check Constraint Does
A CHECK constraint tests a condition every time a row is inserted or updated.
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10,2) CHECK (Price > 0),
Quantity INT CHECK (Quantity >= 0)
);Inserting a negative price fails:
INSERT INTO Products (ProductID, Price, Quantity)
VALUES (1, -50, 10); -- ERROR: Price must be greater than 0Defining a CHECK Constraint
Inline form:
Age INT CHECK (Age >= 18)Table constraint form:
CREATE TABLE Employees (
EmployeeID INT,
Age INT,
CHECK (Age >= 18)
);Named constraints help with identification and management:
CREATE TABLE Employees (
EmployeeID INT,
Age INT,
CONSTRAINT chk_employee_age CHECK (Age >= 18)
);Complex Conditions
A CHECK condition can use AND, OR, BETWEEN, LIKE, and IN.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Amount DECIMAL(10,2),
Status VARCHAR(20),
CHECK (
Amount > 0
AND Status IN ('Pending', 'Shipped', 'Delivered')
)
);Database Support
| Database | CHECK Support |
|---|---|
| PostgreSQL | Full support |
| MySQL | Full support (enforced from 8.0.16 for stored-generated columns; check constraints work in modern versions) |
| SQLite | Parsed but historically ignored before version 3.25 |
Always verify that your database version enforces CHECK.
Real-World Uses
| Rule | Example Condition |
|---|---|
| Positive price | Price > 0 |
| Minimum age | Age >= 18 |
| Valid rating | Rating BETWEEN 1 AND 5 |
| Future date | DeliveryDate > CURRENT_DATE |
| Percentage range | Discount BETWEEN 0 AND 100 |
Pseudocode example:
For each product insert
If Price <= 0:
Reject and show error
If Quantity < 0:
Reject and show error
Else
Accept product
Frequently Asked Questions
Q: Can a CHECK constraint reference other rows? A: No. CHECK evaluates values within the same row.
Q: Can I use a CHECK on a DATE column? A: Yes. Example: CHECK (BirthDate < CURRENT_DATE).
Q: How do I see existing CHECK constraints? A: Use SHOW CREATE TABLE Products; (MySQL) or query information_schema.check_constraints (PostgreSQL).
Summary
CHECKenforces column-level validation rules.- Use it for business logic such as prices, ages, and statuses.
- Named constraints improve maintainability.
- Confirm enforcement behavior for your database engine.
Next Step
See DEFAULT to supply fallback values, or return to the SQL Constraints overview.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for CHECK.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, constraints, check
Related SQL Topics