What is the CHECK Constraint?
The CHECK constraint is used to limit the values that can
be stored in a column. It requires every inserted or updated value to
satisfy a specified condition.
If a value violates the CHECK condition, SQLite rejects the
INSERT or UPDATE operation.
Basic CHECK Syntax
A CHECK constraint can be defined directly next to a column.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
price REAL CHECK (price > 0)
);
INSERT INTO products VALUES
(1, 'Desk Lamp', 850.00),
(2, 'USB Fan', 450.00),
(3, 'Keyboard', 1200.00);
SELECT *
FROM products
ORDER BY product_id;CHECK for Age
A CHECK constraint can ensure that a numeric value falls within a sensible range.
DROP TABLE IF EXISTS members;
CREATE TABLE members (
member_id INTEGER PRIMARY KEY,
member_name TEXT NOT NULL,
age INTEGER CHECK (age >= 18)
);
INSERT INTO members VALUES
(101, 'Aarav', 24),
(102, 'Meera', 31),
(103, 'Kabir', 42);
SELECT *
FROM members
ORDER BY member_id;CHECK with a Range
The BETWEEN operator can be used inside a CHECK condition
when a value must remain within a specific range.
DROP TABLE IF EXISTS exam_results;
CREATE TABLE exam_results (
result_id INTEGER PRIMARY KEY,
student_name TEXT NOT NULL,
score INTEGER CHECK (score BETWEEN 0 AND 100)
);
INSERT INTO exam_results VALUES
(1, 'Isha', 88),
(2, 'Rohan', 76),
(3, 'Nisha', 94);
SELECT *
FROM exam_results
ORDER BY result_id;CHECK with Text Values
CHECK can also restrict a column to a specific set of text values.
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
ticket_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
status TEXT CHECK (
status IN ('Open', 'Pending', 'Closed')
)
);
INSERT INTO tickets VALUES
(1, 'Login problem', 'Open'),
(2, 'Payment question', 'Pending'),
(3, 'Password reset', 'Closed');
SELECT *
FROM tickets
ORDER BY ticket_id;CHECK with Multiple Conditions
Multiple conditions can be combined using operators such as
AND and OR.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT NOT NULL,
age INTEGER,
salary REAL,
CHECK (age >= 18 AND salary >= 15000)
);
INSERT INTO employees VALUES
(1, 'Dev', 25, 42000),
(2, 'Anaya', 29, 51000),
(3, 'Vikram', 34, 68000);
SELECT *
FROM employees
ORDER BY employee_id;CHECK with Multiple Columns
A table-level CHECK constraint can compare values from different columns in the same row.
DROP TABLE IF EXISTS events;
CREATE TABLE events (
event_id INTEGER PRIMARY KEY,
event_name TEXT NOT NULL,
start_hour INTEGER NOT NULL,
end_hour INTEGER NOT NULL,
CHECK (end_hour > start_hour)
);
INSERT INTO events VALUES
(1, 'Morning Workshop', 9, 12),
(2, 'Design Session', 13, 16),
(3, 'Evening Seminar', 17, 20);
SELECT *
FROM events
ORDER BY event_id;CHECK with Quantity and Price
CHECK can validate relationships between numeric columns, such as ensuring that quantity and price are positive.
DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
item_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
CHECK (quantity > 0),
CHECK (unit_price > 0)
);
INSERT INTO order_items VALUES
(1, 'Notebook', 3, 120.00),
(2, 'Desk Organizer', 2, 350.00),
(3, 'Pen Set', 5, 90.00);
SELECT
item_id,
product_name,
quantity,
unit_price,
quantity * unit_price AS total
FROM order_items
ORDER BY item_id;CHECK with INSERT and UPDATE
CHECK constraints are evaluated during both INSERT and UPDATE operations.
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
item_id INTEGER PRIMARY KEY,
item_name TEXT NOT NULL,
stock INTEGER CHECK (stock >= 0)
);
INSERT INTO inventory VALUES
(1, 'Camera Tripod', 12),
(2, 'LED Panel', 8),
(3, 'Microphone Stand', 15);
UPDATE inventory
SET stock = 20
WHERE item_id = 2;
SELECT *
FROM inventory
ORDER BY item_id;Named CHECK Constraint
A table-level CHECK constraint can be given a meaningful name, making the rule easier to identify.
DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
course_id INTEGER PRIMARY KEY,
course_name TEXT NOT NULL,
duration_hours INTEGER,
CONSTRAINT valid_duration
CHECK (duration_hours > 0 AND duration_hours <= 500)
);
INSERT INTO courses VALUES
(1, 'Database Fundamentals', 40),
(2, 'Mechanical Design', 80),
(3, 'Industrial Automation', 120);
SELECT *
FROM courses
ORDER BY course_id;CHECK with Date Values
SQLite stores dates in several formats. A CHECK condition can compare ISO-format date strings because they sort chronologically.
DROP TABLE IF EXISTS bookings;
CREATE TABLE bookings (
booking_id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
start_date TEXT NOT NULL,
end_date TEXT NOT NULL,
CHECK (end_date >= start_date)
);
INSERT INTO bookings VALUES
(1, 'Aman', '2026-08-10', '2026-08-12'),
(2, 'Priya', '2026-09-03', '2026-09-06'),
(3, 'Rahul', '2026-10-15', '2026-10-20');
SELECT *
FROM bookings
ORDER BY booking_id;Common CHECK Conditions
| Condition | Purpose | Example |
|---|---|---|
| Greater than | Require a value above a limit | CHECK (price > 0) |
| Range | Restrict values to a range | CHECK (score BETWEEN 0 AND 100) |
| Specific values | Allow selected values | CHECK (status IN ('Open','Closed')) |
| Multiple columns | Compare columns | CHECK (end_date >= start_date) |
| AND / OR | Combine conditions | CHECK (age >= 18 AND age <= 65) |
Advantages of CHECK
- Prevents invalid values from being stored.
- Improves data quality and consistency.
- Can validate numeric ranges.
- Can restrict columns to specific values.
- Can compare values from multiple columns.
- Works during both INSERT and UPDATE operations.
Best Practices
- Use CHECK for rules that should always be true for stored data.
- Keep conditions simple and easy to understand.
- Use meaningful constraint names when appropriate.
- Combine CHECK with NOT NULL when a value is mandatory.
- Use CHECK to enforce valid ranges and allowed values.
- Test both INSERT and UPDATE operations against your constraints.
The CHECK constraint helps protect the quality of your database by ensuring that stored values satisfy predefined rules.
🧪 Test Your SQL Code
Edit the SQL code on the left and click "Run Code" to see the result on the right.
Click "Run Code" to see the result here.