CHECK & DEFAULT Constraints: Data Validation and Fallback Values

The final pieces of the constraints puzzle are CHECK and DEFAULT. CHECK constraints let you define custom rules that data must follow—like "price must be positive" or "age must be 18 or older." DEFAULT constraints provide automatic fallback values when data isn't supplied. Together, they prevent bad data at the source and make your database more user-friendly. In this lesson, you'll learn how to write validation rules, set default values, and build databases that enforce business logic automatically.

📚 What You'll Learn

• What CHECK constraints do and how they enforce business rules
• How to write CHECK constraint expressions
• What DEFAULT constraints do and when to use them
• How DEFAULT values work during inserts
• How to combine CHECK and DEFAULT constraints
• Common CHECK constraint patterns (ranges, enums, comparisons)
• How constraints affect inserts and updates
• Best practices for data validation at the database level
• When to use constraints vs. application-level validation

1. DEFAULT: Automatic Fallback Values

Example: Creating Timestamps Automatically

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'draft',
    views INT DEFAULT 0
);

-- Insert without specifying created_at, status, or views
INSERT INTO posts (title, content)
VALUES ('My First Post', 'This is the content...');

-- Database automatically fills in the defaults:
SELECT * FROM posts;
-- +------+---------------+---------+---------------------+--------+-------+
-- | id   | title         | content | created_at          | status | views |
-- +------+---------------+---------+---------------------+--------+-------+
-- | 1    | My First Post | ...     | 2024-12-16 10:30:00 | draft  | 0     |
-- +------+---------------+---------+---------------------+--------+-------+

What happened? We only provided title and content, but the database automatically:

• ✓ Set created_at to the current timestamp
• ✓ Set status to 'draft'
• ✓ Set views to 0

Common DEFAULT Values

Overriding DEFAULT Values

-- You can always override defaults by providing your own value
INSERT INTO posts (title, content, status, views)
VALUES ('Featured Post', 'Special content...', 'published', 1000);

-- Result: Uses your values instead of defaults
-- status = 'published' (not 'draft')
-- views = 1000 (not 0)

2. CHECK: Enforcing Business Rules

Example: Positive Prices Only

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    CHECK (price > 0),              -- Price must be positive
    CHECK (stock_quantity >= 0)     -- Stock can't be negative
);

-- ✓ Success: price is positive
INSERT INTO products (name, price, stock_quantity)
VALUES ('Laptop', 999.99, 50);

-- ❌ Fails: negative price
INSERT INTO products (name, price)
VALUES ('Mouse', -10.00);
-- ERROR: Check constraint violated

-- ❌ Fails: negative stock
INSERT INTO products (name, price, stock_quantity)
VALUES ('Keyboard', 79.99, -5);
-- ERROR: Check constraint violated

CHECK Constraint Syntax

-- Inline (unnamed)
CREATE TABLE products (
    price DECIMAL(10, 2) CHECK (price > 0)
);

-- Named constraint (better practice)
CREATE TABLE products (
    price DECIMAL(10, 2),
    CONSTRAINT chk_positive_price CHECK (price > 0)
);

3. Common CHECK Constraint Patterns

Pattern 1: Range Validation

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    gpa DECIMAL(3, 2),
    CHECK (age >= 16 AND age <= 100),     -- Age must be 16-100
    CHECK (gpa >= 0.0 AND gpa <= 4.0)     -- GPA must be 0.0-4.0
);

Pattern 2: Enumerated Values (Limited Options)

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

-- ✓ Success: valid status
INSERT INTO orders (status) VALUES ('shipped');

-- ❌ Fails: invalid status
INSERT INTO orders (status) VALUES ('unknown');
-- ERROR: Check constraint violated

Pattern 3: Column Comparison

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CHECK (end_date >= start_date)  -- End must be after (or same as) start
);

-- ✓ Success: end date after start date
INSERT INTO events (event_name, start_date, end_date)
VALUES ('Conference', '2024-06-01', '2024-06-03');

-- ❌ Fails: end date before start date
INSERT INTO events (event_name, start_date, end_date)
VALUES ('Workshop', '2024-06-05', '2024-06-02');
-- ERROR: Check constraint violated

Pattern 4: String Pattern Validation

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    CHECK (LENGTH(username) >= 3),              -- Username at least 3 chars
    CHECK (email LIKE '%@%')                    -- Email must contain @
);

-- ✓ Success: meets all requirements
INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com');

-- ❌ Fails: username too short
INSERT INTO users (username, email)
VALUES ('ab', 'ab@example.com');
-- ERROR: Check constraint violated

-- ❌ Fails: invalid email format
INSERT INTO users (username, email)
VALUES ('bob', 'bobexample.com');
-- ERROR: Check constraint violated

Pattern 5: Conditional Logic

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    is_manager BOOLEAN NOT NULL DEFAULT FALSE,
    salary DECIMAL(10, 2) NOT NULL,
    -- Managers must earn at least 50,000
    CHECK (is_manager = FALSE OR salary >= 50000)
);

-- ✓ Success: regular employee with any salary
INSERT INTO employees (name, is_manager, salary)
VALUES ('Alice', FALSE, 40000);

-- ✓ Success: manager with high salary
INSERT INTO employees (name, is_manager, salary)
VALUES ('Bob', TRUE, 75000);

-- ❌ Fails: manager with low salary
INSERT INTO employees (name, is_manager, salary)
VALUES ('Carol', TRUE, 35000);
-- ERROR: Check constraint violated

4. Combining DEFAULT and CHECK Constraints

DEFAULT and CHECK work perfectly together: DEFAULT provides a safe value, CHECK ensures all values (default or provided) are valid.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    discount_percent INT DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ✓ Minimal insert: only required field (name, price)
INSERT INTO products (name, price)
VALUES ('Widget', 29.99);
-- Result: discount_percent=0, stock_quantity=0, is_active=TRUE, created_at=now

-- ✓ Override some defaults
INSERT INTO products (name, price, discount_percent, stock_quantity)
VALUES ('Gadget', 49.99, 20, 100);

-- ❌ Fails: invalid discount (even though trying to use default)
INSERT INTO products (name, price, discount_percent)
VALUES ('Item', 19.99, 150);
-- ERROR: discount_percent must be 0-100

5. Adding and Removing Constraints

Adding DEFAULT to Existing Column

-- Add DEFAULT value
ALTER TABLE products
MODIFY stock_quantity INT DEFAULT 0;

-- Note: This only affects NEW inserts, not existing rows

Adding CHECK Constraint

-- Add CHECK constraint (named)
ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- WARNING: This fails if existing data violates the constraint!

Removing Constraints

-- Remove CHECK constraint (by name)
ALTER TABLE products
DROP CHECK chk_positive_price;

-- Remove DEFAULT (make column require explicit value)
ALTER TABLE products
ALTER COLUMN stock_quantity DROP DEFAULT;

6. Best Practices: When to Use Constraints

✅ Use DEFAULT When:

• Most records will use the same value (status='pending', active=TRUE)
• You want automatic timestamps (created_at, updated_at)
• You want to simplify INSERT statements
• The default makes sense for new records

✅ Use CHECK When:

• Values must follow business rules (age >= 18, price > 0)
• You have a limited set of valid options (status in list)
• Relationships between columns must be maintained (end_date >= start_date)
• Data quality is critical and cannot be trusted to applications

⚠️ Limitations of CHECK

7. 🎯 Practice Exercises

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    discount_percent INT DEFAULT 0,
    stock_quantity INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT chk_positive_price CHECK (price > 0),
    CONSTRAINT chk_valid_discount CHECK (discount_percent >= 0 AND discount_percent <= 100),
    CONSTRAINT chk_valid_stock CHECK (stock_quantity >= 0)
);

-- Test cases
-- ✓ Success: minimal insert
INSERT INTO products (name, price)
VALUES ('Laptop', 999.99);

-- ✓ Success: with discount and stock
INSERT INTO products (name, price, discount_percent, stock_quantity)
VALUES ('Mouse', 29.99, 15, 50);

-- ❌ Fails: negative price
INSERT INTO products (name, price)
VALUES ('Keyboard', -10.00);

-- ❌ Fails: invalid discount
INSERT INTO products (name, price, discount_percent)
VALUES ('Monitor', 299.99, 150);

-- ❌ Fails: negative stock
INSERT INTO products (name, price, stock_quantity)
VALUES ('Webcam', 79.99, -5);

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    capacity INT NOT NULL,
    status VARCHAR(20) DEFAULT 'scheduled',
    
    -- Date validation
    CONSTRAINT chk_valid_date_range CHECK (end_date >= start_date),
    
    -- Capacity must be positive
    CONSTRAINT chk_positive_capacity CHECK (capacity > 0),
    
    -- Status must be valid
    CONSTRAINT chk_valid_status CHECK (
        status IN ('scheduled', 'ongoing', 'completed', 'cancelled')
    )
);

-- Test cases
-- ✓ Success: valid event
INSERT INTO events (event_name, start_date, end_date, capacity)
VALUES ('Tech Conference', '2024-06-01', '2024-06-03', 500);

-- ✓ Success: single-day event (end = start)
INSERT INTO events (event_name, start_date, end_date, capacity)
VALUES ('Workshop', '2024-07-15', '2024-07-15', 30);

-- ❌ Fails: end date before start date
INSERT INTO events (event_name, start_date, end_date, capacity)
VALUES ('Invalid Event', '2024-06-10', '2024-06-05', 100);

-- ❌ Fails: negative capacity
INSERT INTO events (event_name, start_date, end_date, capacity)
VALUES ('Bad Event', '2024-06-01', '2024-06-02', -10);

-- ❌ Fails: invalid status
INSERT INTO events (event_name, start_date, end_date, capacity, status)
VALUES ('Event', '2024-06-01', '2024-06-02', 50, 'unknown');

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT,
    account_status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Username validation
    CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3),
    
    -- Email validation (basic)
    CONSTRAINT chk_email_format CHECK (email LIKE '%@%'),
    
    -- Age validation (only if provided)
    CONSTRAINT chk_minimum_age CHECK (age IS NULL OR age >= 13),
    
    -- Status validation
    CONSTRAINT chk_valid_status CHECK (
        account_status IN ('active', 'suspended', 'deleted')
    )
);

-- Test cases
-- ✓ Success: valid user without age
INSERT INTO users (username, email)
VALUES ('alice123', 'alice@example.com');

-- ✓ Success: valid user with age
INSERT INTO users (username, email, age)
VALUES ('bob456', 'bob@example.com', 25);

-- ❌ Fails: username too short
INSERT INTO users (username, email)
VALUES ('ab', 'ab@example.com');

-- ❌ Fails: invalid email (no @)
INSERT INTO users (username, email)
VALUES ('carol', 'carolexample.com');

-- ❌ Fails: age too young
INSERT INTO users (username, email, age)
VALUES ('dave', 'dave@example.com', 10);

-- ❌ Fails: invalid status
INSERT INTO users (username, email, account_status)
VALUES ('eve', 'eve@example.com', 'banned');

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    salary DECIMAL(10, 2) NOT NULL,
    commission_rate DECIMAL(4, 2) DEFAULT 0,
    is_manager BOOLEAN DEFAULT FALSE,
    employment_status VARCHAR(20) DEFAULT 'full-time',
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    
    -- Salary range validation
    CONSTRAINT chk_salary_range CHECK (
        salary BETWEEN 30000 AND 500000
    ),
    
    -- Commission validation
    CONSTRAINT chk_commission_range CHECK (
        commission_rate >= 0 AND commission_rate <= 30
    ),
    
    -- Manager minimum salary
    CONSTRAINT chk_manager_salary CHECK (
        is_manager = FALSE OR salary >= 60000
    ),
    
    -- Hire date validation (not in future)
    CONSTRAINT chk_hire_date CHECK (
        hire_date <= CURRENT_DATE
    ),
    
    -- Employment status validation
    CONSTRAINT chk_employment_status CHECK (
        employment_status IN ('full-time', 'part-time', 'contractor')
    )
);

-- Test cases
-- ✓ Success: regular employee
INSERT INTO employees (name, email, salary)
VALUES ('Alice Johnson', 'alice@company.com', 55000);

-- ✓ Success: manager with high salary
INSERT INTO employees (name, email, salary, is_manager, commission_rate)
VALUES ('Bob Smith', 'bob@company.com', 85000, TRUE, 10);

-- ❌ Fails: salary too low
INSERT INTO employees (name, email, salary)
VALUES ('Carol White', 'carol@company.com', 25000);

-- ❌ Fails: manager with low salary
INSERT INTO employees (name, email, salary, is_manager)
VALUES ('Dave Brown', 'dave@company.com', 45000, TRUE);

-- ❌ Fails: commission too high
INSERT INTO employees (name, email, salary, commission_rate)
VALUES ('Eve Davis', 'eve@company.com', 50000, 35);

-- ❌ Fails: future hire date
INSERT INTO employees (name, email, salary, hire_date)
VALUES ('Frank Wilson', 'frank@company.com', 60000, '2025-12-31');

-- ❌ Fails: invalid employment status
INSERT INTO employees (name, email, salary, employment_status)
VALUES ('Grace Lee', 'grace@company.com', 50000, 'intern');

📝 Key Takeaways

• DEFAULT provides automatic fallback values when data isn't supplied
• CHECK enforces custom business rules and validation
• DEFAULT values are used during INSERT if column is omitted
• CHECK constraints are evaluated on INSERT and UPDATE
• CHECK expressions must evaluate to TRUE for operation to succeed
• Common DEFAULT patterns: CURRENT_TIMESTAMP, 0, FALSE, 'pending'
• Common CHECK patterns: ranges, enums, comparisons, string validation
• CHECK can only reference columns in the same row
• Named constraints (CONSTRAINT name CHECK ...) are better for maintenance
• Combine DEFAULT and CHECK for safe defaults with validation
• Database-level validation is more reliable than app-level validation
• CHECK constraints document business rules in the schema
• Use IS NULL OR pattern to allow NULL but validate if present