SQL Notes
How to use the CHECK constraint in SQL to validate column values against custom conditions, enforce business rules, and maintain data correctness with practical examples and detailed rules.
Imagine you are building an employee database and someone accidentally inserts an age of -5 or a salary of negative ten thousand. Without any validation, the database happily stores that nonsense. The CHECK constraint prevents exactly this kind of invalid data from ever entering your tables.
The CHECK constraint tests a condition every time a row is inserted or updated. If the condition evaluates to FALSE, the database rejects the operation entirely. It is like having a bouncer at the door who only lets valid data through.
In professional database development, CHECK constraints are considered essential for data quality. They catch mistakes before they become problems — a negative price that confuses the billing system, an age of 200 that breaks analytics, or an invalid status that causes application errors downstream. By encoding these rules directly in the database schema, you create a safety net that protects your data regardless of which application, script, or developer is writing to the table.
Basic Syntax
You define a CHECK constraint by specifying a boolean condition that must be true for every row:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(200) NOT NULL,
Price DECIMAL(10,2) CHECK (Price > 0),
Quantity INT CHECK (Quantity >= 0),
Discount DECIMAL(5,2) CHECK (Discount BETWEEN 0 AND 50)
);Now let us try some inserts:
-- Valid insert
INSERT INTO Products VALUES (1, 'Laptop', 45000.00, 10, 15.00);
-- FAILS: Price must be greater than 0
INSERT INTO Products VALUES (2, 'Mouse', -500.00, 20, 0);
-- FAILS: Quantity cannot be negative
INSERT INTO Products VALUES (3, 'Keyboard', 800.00, -5, 10.00);
-- FAILS: Discount must be between 0 and 50
INSERT INTO Products VALUES (4, 'Monitor', 12000.00, 5, 75.00);Inline vs Table-Level CHECK
You can define CHECK constraints in two ways:
Inline (Column-Level)
Placed directly after the column definition:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Age INT CHECK (Age >= 18),
Salary DECIMAL(10,2) CHECK (Salary > 0)
);Table-Level
Placed after all column definitions, useful when the constraint involves multiple columns:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
StartDate DATE,
EndDate DATE,
MinSalary DECIMAL(10,2),
MaxSalary DECIMAL(10,2),
CHECK (EndDate > StartDate),
CHECK (MaxSalary >= MinSalary)
);Named CHECK Constraints
Giving your constraints names makes them easier to identify in error messages and simpler to modify later:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT,
CGPA DECIMAL(3,2),
Semester INT,
CONSTRAINT chk_student_age CHECK (Age BETWEEN 16 AND 60),
CONSTRAINT chk_cgpa_range CHECK (CGPA BETWEEN 0.00 AND 10.00),
CONSTRAINT chk_semester CHECK (Semester BETWEEN 1 AND 8)
);When a violation occurs, the error message includes the constraint name, making debugging much easier.
Complex CHECK Conditions
CHECK supports all comparison operators and logical operators, so you can build sophisticated validation rules:
Using AND, OR
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Amount DECIMAL(10,2),
Status VARCHAR(20),
PaymentMethod VARCHAR(20),
CHECK (Amount > 0 AND Amount < 1000000),
CHECK (Status IN ('Pending', 'Confirmed', 'Shipped', 'Delivered', 'Cancelled')),
CHECK (PaymentMethod IN ('Cash', 'Card', 'UPI', 'NetBanking'))
);Using LIKE for Pattern Matching
CREATE TABLE Contacts (
ContactID INT PRIMARY KEY,
Email VARCHAR(255),
Phone VARCHAR(15),
CHECK (Email LIKE '%@%.%'),
CHECK (Phone LIKE '___________') -- Exactly 10 digits
);Multi-Column Conditions
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE,
Budget DECIMAL(12,2),
SpentAmount DECIMAL(12,2) DEFAULT 0,
CHECK (EndDate IS NULL OR EndDate > StartDate),
CHECK (SpentAmount <= Budget)
);Real-World Example: E-Commerce Platform
CREATE TABLE CustomerOrders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATE DEFAULT CURRENT_DATE,
DeliveryDate DATE,
TotalAmount DECIMAL(10,2) NOT NULL,
ItemCount INT NOT NULL,
Status VARCHAR(20) DEFAULT 'Pending',
Rating INT,
CONSTRAINT chk_amount CHECK (TotalAmount > 0),
CONSTRAINT chk_items CHECK (ItemCount > 0),
CONSTRAINT chk_delivery CHECK (DeliveryDate IS NULL OR DeliveryDate >= OrderDate),
CONSTRAINT chk_status CHECK (Status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Returned')),
CONSTRAINT chk_rating CHECK (Rating IS NULL OR Rating BETWEEN 1 AND 5)
);This ensures orders always have positive amounts, valid statuses, delivery dates that make sense, and ratings within the expected range.
Real-World Example: Employee Management
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL,
Age INT NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
Department VARCHAR(50),
JoinDate DATE DEFAULT CURRENT_DATE,
CONSTRAINT chk_emp_age CHECK (Age BETWEEN 18 AND 65),
CONSTRAINT chk_emp_salary CHECK (Salary >= 15000),
CONSTRAINT chk_emp_email CHECK (Email LIKE '%@%'),
CONSTRAINT chk_emp_dept CHECK (Department IN ('Engineering', 'Marketing', 'Sales', 'HR', 'Finance'))
);Adding CHECK to an Existing Table
-- Add a CHECK constraint to an existing table
ALTER TABLE Employees
ADD CONSTRAINT chk_salary_positive CHECK (Salary > 0);
-- Add a multi-column constraint
ALTER TABLE Projects
ADD CONSTRAINT chk_dates_valid CHECK (EndDate > StartDate);Dropping a CHECK Constraint
MySQL
ALTER TABLE Employees
DROP CHECK chk_salary_positive;SQL Server and PostgreSQL
ALTER TABLE Employees
DROP CONSTRAINT chk_salary_positive;Database Support Differences
| Database | CHECK Support |
|---|---|
| PostgreSQL | Full support since early versions |
| SQL Server | Full support |
| MySQL | Enforced from version 8.0.16 onwards |
| SQLite | Full support |
| Oracle | Full support |
Important note: MySQL versions before 8.0.16 accepted CHECK syntax but silently ignored it. Always verify your MySQL version if you rely on CHECK constraints.
CHECK vs Application Validation
| Aspect | CHECK Constraint | Application Validation |
|---|---|---|
| Where it runs | Database engine | Application code |
| Bypassed by direct SQL | No | Yes |
| Performance | Very fast | Depends on implementation |
| Error messages | Generic | Can be user-friendly |
| Complex logic | Limited to SQL expressions | Unlimited |
The best approach is to use both. Application validation provides user-friendly error messages and catches errors early, while CHECK constraints serve as the last line of defense at the database level.
Common Mistakes to Avoid
Mistake 1: Referencing other tables in CHECK. CHECK constraints cannot reference other tables. For cross-table validation, use triggers or foreign keys.
-- This will NOT work
CHECK (DeptID IN (SELECT DeptID FROM Departments))Mistake 2: Forgetting NULL behavior. CHECK evaluates to TRUE when the value is NULL (because NULL is unknown, not false). If you want to prevent NULLs, combine CHECK with NOT NULL.
Mistake 3: Making constraints too restrictive. Do not hard-code values that might change. For example, checking status against a fixed list means you need to alter the constraint every time you add a new status.
Best Practices
- Name all CHECK constraints for clarity in error messages
- Keep conditions simple — complex business logic belongs in triggers or application code
- Combine with NOT NULL when NULL values should not pass the check
- Use IN for enumerated values rather than multiple OR conditions
- Test edge cases including NULL, boundary values, and empty strings
- Document the business rule each constraint enforces with comments
CHECK constraints are your database's built-in data validator. They catch bad data before it can cause problems downstream, making your entire application more robust and trustworthy.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for CHECK Constraint in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, check, constraint
Related SQL Complete Guide Topics