MySQL CHECK

Validating data with custom conditions

✅ What is CHECK?

The CHECK constraint validates data based on custom conditions before insertion or update. It ensures column values meet specific requirements, like age being positive or status matching predefined values, maintaining data quality automatically. 


-- Creating a table with CHECK constraint
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18),
    salary DECIMAL(10,2) CHECK (salary > 0)
);

Output:

Table created with CHECK constraints on age and salary.

Key CHECK Concepts

🎯

Custom Rules

Define your own conditions 

CHECK (age >= 18)
🔢

Range Validation

Limit values to ranges 

CHECK (price BETWEEN 0 AND 1000)
📝

List Validation

Restrict to specific values 

CHECK (status IN ('active','inactive'))
🔍

Complex Logic

Combine multiple conditions 

CHECK (age >= 18 AND age <= 65)

🔹 Creating CHECK Constraint

Define CHECK constraints when creating tables to enforce business rules at the database level. This prevents invalid data from entering your database, catching errors before they cause problems. CHECK constraints can validate ranges, lists, or complex logical conditions on column values. 

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

Output:

✓ Table created with CHECK constraints ensuring positive price and non-negative stock

🔹 CHECK with IN Operator

Use the IN operator within CHECK constraints to limit column values to a predefined list. This is perfect for status fields, categories, or any column that should only accept specific values, providing built-in validation without application-level code. 

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    status VARCHAR(20) CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    priority VARCHAR(10) CHECK (priority IN ('low', 'medium', 'high'))
);

Output:

✓ Table created with CHECK constraints limiting status and priority to valid values

🔹 CHECK with Range Validation

Range validation using BETWEEN or comparison operators ensures numeric values fall within acceptable limits. This is essential for fields like age, percentage, rating, or quantity where values outside a range would be meaningless or invalid for your business logic. 

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 5 AND 100),
    grade_percentage DECIMAL(5,2) CHECK (grade_percentage >= 0 AND grade_percentage <= 100)
);

Output:

✓ Table created with range CHECK constraints on age and grade percentage

🔹 Named CHECK Constraint

Naming CHECK constraints makes them easier to identify in error messages and manage later. Use descriptive names that explain what the constraint validates, which helps with debugging and documentation. Named constraints can be easily dropped or modified when business rules change. 

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary DECIMAL(10,2),
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_salary CHECK (salary >= 15000 AND salary <= 500000)
);

Output:

✓ Table created with named CHECK constraints 'chk_age' and 'chk_salary'

🔹 Multiple Column CHECK

CHECK constraints can validate relationships between multiple columns in the same row. This enables complex business rules like ensuring end dates are after start dates, or discount amounts don't exceed original prices, maintaining logical consistency across related fields. 

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

Output:

✓ Table created with CHECK ensuring end_date is not before start_date

🔹 Adding CHECK to Existing Table

Add CHECK constraints to existing tables using ALTER TABLE to enforce new business rules. Before adding the constraint, ensure all existing data meets the condition, or MySQL will reject the operation. You may need to update invalid data first. 

-- Add CHECK constraint to existing table
ALTER TABLE products 
ADD CONSTRAINT chk_price CHECK (price > 0);

-- Add CHECK with multiple conditions
ALTER TABLE employees 
ADD CONSTRAINT chk_employee_age CHECK (age >= 18 AND age <= 70);

Output:

✓ CHECK constraints added to existing tables

🔹 Testing CHECK Constraint

When you attempt to insert or update data that violates a CHECK constraint, MySQL rejects the operation and returns an error. This automatic validation protects your data integrity without requiring application-level checks, ensuring only valid data enters your database. 

-- Valid insert (passes CHECK)
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (1, 'Laptop', 999.99, 50);

-- Invalid insert (violates CHECK)
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (2, 'Mouse', -10.00, 100);
-- Error: Check constraint 'chk_price' is violated

Output:

✓ First insert successful

✗ Second insert failed: CHECK constraint violated

🔹 Dropping CHECK Constraint

Remove CHECK constraints when business rules change or you need to temporarily disable validation. Use the constraint name to drop it, which is why naming your CHECK constraints is important for easier management and maintenance of your database schema. 

-- Drop CHECK constraint by name
ALTER TABLE employees 
DROP CHECK chk_age;

Output:

✓ CHECK constraint 'chk_age' dropped successfully

🧠 Test Your Knowledge

What does a CHECK constraint do?