Step 8
7 min read

CHECK Constraint

Learn how to validate data with rules - explained with simple everyday examples.

What You Will Learn

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows
  • What CHECK constraint is (in simple words)
  • How to validate data
  • Common validation rules
  • Easy examples

What is CHECK?

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

CHECK is like a rule checker. It makes sure data follows certain rules before allowing it into the table.

Think of it like:

  • Age verification (must be 18 or older)
  • Price validation (must be positive, cannot be negative)
  • Grade validation (must be A, B, C, D, or F)

Real-Life Examples

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Online Shopping:

  • Price must be greater than 0 (cannot sell for negative price)
  • Quantity must be at least 1 (cannot order 0 items)
  • Rating must be between 1 and 5 (cannot rate 10 stars)

School System:

  • Age must be between 5 and 100 (reasonable ages)
  • GPA must be between 0.0 and 4.0 (valid range)
  • Attendance must be 0 to 100 percent

How to Use CHECK

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Example 1: Product Prices

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(200) NOT NULL, price DECIMAL(10, 2) CHECK (price > 0) );

What this does:

  • Price must be greater than 0
  • Cannot insert negative prices
  • Cannot insert price of 0

Testing it:

-- This works: INSERT INTO products (product_name, price) VALUES ('Laptop', 999.99); -- OK: 999.99 > 0 -- This fails: INSERT INTO products (product_name, price) VALUES ('Free Item', 0); -- ERROR: 0 is not > 0 INSERT INTO products (product_name, price) VALUES ('Bad Item', -50); -- ERROR: -50 is not > 0

Example 2: Age Validation

CREATE TABLE students ( student_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, age INTEGER CHECK (age >= 5 AND age <= 100) );

What this does:

  • Age must be between 5 and 100
  • Cannot insert age of 3 or 150

Testing it:

-- This works: INSERT INTO students (name, age) VALUES ('John', 20); -- OK: 20 is between 5 and 100 -- This fails: INSERT INTO students (name, age) VALUES ('Baby', 2); -- ERROR: 2 < 5 INSERT INTO students (name, age) VALUES ('Ancient', 200); -- ERROR: 200 > 100

Example 3: Rating System

CREATE TABLE reviews ( review_id SERIAL PRIMARY KEY, product_id INTEGER, rating INTEGER CHECK (rating >= 1 AND rating <= 5), comment TEXT );

What this does:

  • Rating must be 1, 2, 3, 4, or 5
  • Cannot rate 0 or 10

Common CHECK Rules

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Positive Numbers

CHECK (price > 0) CHECK (quantity > 0) CHECK (salary > 0)

Non-Negative Numbers

CHECK (age >= 0) CHECK (stock >= 0) CHECK (balance >= 0)

Range Validation

CHECK (age BETWEEN 18 AND 65) CHECK (rating BETWEEN 1 AND 5) CHECK (percentage BETWEEN 0 AND 100)

List of Allowed Values

CHECK (status IN ('pending', 'approved', 'rejected')) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')) CHECK (size IN ('S', 'M', 'L', 'XL'))

Comparison Between Columns

CHECK (start_date < end_date) CHECK (min_price < max_price) CHECK (discount <= original_price)

Real-World Examples

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Example 1: E-commerce

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(200) NOT NULL, price DECIMAL(10, 2) CHECK (price > 0), discount DECIMAL(10, 2) CHECK (discount >= 0 AND discount <= price), stock INTEGER CHECK (stock >= 0) );

Rules:

  • Price must be positive
  • Discount cannot exceed price
  • Stock cannot be negative

Example 2: Event Booking

CREATE TABLE events ( event_id SERIAL PRIMARY KEY, event_name VARCHAR(200) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, max_attendees INTEGER CHECK (max_attendees > 0), CHECK (end_date >= start_date) );

Rules:

  • Must have at least 1 attendee
  • End date cannot be before start date

Example 3: Grades

CREATE TABLE student_grades ( grade_id SERIAL PRIMARY KEY, student_id INTEGER NOT NULL, grade VARCHAR(2) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')), gpa DECIMAL(3, 2) CHECK (gpa >= 0.0 AND gpa <= 4.0) );

Rules:

  • Grade must be A, B, C, D, or F
  • GPA must be between 0.0 and 4.0

Common Mistakes

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Mistake 1: No Validation

Wrong:

CREATE TABLE products ( price DECIMAL(10, 2) ); -- Can insert negative price (bad!) INSERT INTO products (price) VALUES (-100);

Correct:

CREATE TABLE products ( price DECIMAL(10, 2) CHECK (price > 0) ); -- Cannot insert negative price INSERT INTO products (price) VALUES (-100); -- ERROR

Mistake 2: Too Strict Rules

Wrong:

CHECK (age = 20) -- Only allows age 20!

Correct:

CHECK (age >= 18 AND age <= 100) -- Allows range

Summary

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Think of CHECK like:

  • Age verification at a movie theater (must be 18+)
  • Weight limit on an elevator (max 1000 lbs)
  • Speed limit on a road (max 65 mph)
  • Temperature setting on an oven (50-500 degrees)

Key points:

  1. CHECK validates data before inserting
  2. Like rules or conditions
  3. Prevents invalid data
  4. Can check ranges (1-5)
  5. Can check positive numbers (> 0)
  6. Can check allowed values (A, B, C)
  7. Can compare columns
  8. Protects data quality

What Comes Next

Products Table with CHECK Constraints
product_idproduct_nameprice (CHECK > 0)stock (CHECK >= 0)
1Laptop999.9950
2Mouse19.99100
3Keyboard49.990
3 rows
Common CHECK Constraint Examples
Use CaseCHECK RuleWhat It Prevents
Priceprice > 0Negative or zero prices
Ageage BETWEEN 18 AND 65Invalid ages
Ratingrating IN (1,2,3,4,5)Ratings outside 1-5
Stockstock >= 0Negative inventory
Datesend_date >= start_dateEnd before start
5 rows

Congratulations! You have learned all the main constraints (Primary Key, Foreign Key, UNIQUE, NOT NULL, CHECK). These keep your database clean and organized.

Finished this topic?

Mark it complete to track your progress and maintain your streak!

Previous
NOT NULL
Next
INNER JOIN
SkillsetMaster - AI, Web Development & Data Analytics Courses