Item 1: Favor CHECK constraints over application-level validation

by

The Problem: Application-Level Validation is Fragile

Picture this: You’re building an e-commerce platform. Your product pricing logic is carefully implemented in your API layer:

def create_product(name, price):
    if price <= 0:
        raise ValueError("Price must be positive")
    
    db.execute(
        "INSERT INTO products (name, price) VALUES (%s, %s)",
        (name, price)
    )

Looks solid, right? But here’s what can go wrong:

Scenario 1: The Midnight Migration

Your DevOps engineer runs a data migration script at 2 AM to fix some corrupted records:

-- Oops, this bypasses your Python validation entirely
UPDATE products SET price = -10.00 WHERE id = 12345;

Scenario 2: The Background Job

Your inventory sync job pulls data from a legacy system:

# This script doesn't use your API - it writes directly to the database
for item in legacy_system.get_items():
    db.execute(
        "INSERT INTO products (name, price) VALUES (%s, %s)",
        (item.name, item.price)  # Could be negative!
    )

Scenario 3: The Database Admin

A database administrator runs a bulk update to apply a discount:

-- Accidentally uses subtraction instead of multiplication
UPDATE products SET price = price - 0.9;  -- Should be price * 0.9

Scenario 4: The Bug

A developer introduces a bug that accidentally clears validation:

def update_product(id, price):
    # TODO: Add validation
    db.execute("UPDATE products SET price = %s WHERE id = %s", (price, id))

The fundamental problem: Application-level validation is a suggestion, not a guarantee. The database has no idea that prices should be positive.

The Solution: CHECK Constraints as the Final Line of Defense

PostgreSQL CHECK constraints are enforced at the database level, making them impossible to bypass:

ALTER TABLE products 
ADD CONSTRAINT price_must_be_positive 
CHECK (price > 0);

Now, every single write operation to this table goes through the same validation:

-- API insert: ✓ Validated
INSERT INTO products (name, price) VALUES ('Widget', 19.99);

-- Migration script: ✓ Validated
UPDATE products SET price = -10.00 WHERE id = 12345;
-- ERROR: new row for relation "products" violates check constraint "price_must_be_positive"

-- Background job: ✓ Validated
INSERT INTO products (name, price) VALUES ('Legacy Item', -5.00);
-- ERROR: new row for relation "products" violates check constraint "price_must_be_positive"

-- DBA bulk update: ✓ Validated
UPDATE products SET price = price - 0.9;
-- ERROR: new row for relation "products" violates check constraint "price_must_be_positive"

Key insight: You still write application-level validation for user experience (instant feedback, helpful error messages), but the CHECK constraint is your guarantee that invalid data never enters the database.

Real-World Examples

Example 1: Dates and Time Ranges

A common mistake is allowing end dates before start dates:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    CHECK (end_date IS NULL OR end_date >= start_date)
);

Notice the end_date IS NULL OR part – this allows events without an end date while still enforcing the constraint when one is provided.

Test it:

-- Valid: No end date
INSERT INTO events (id, name, start_date) 
VALUES (1, 'Conference', '2024-06-01');
-- ✓ Success

-- Valid: End date after start date
INSERT INTO events (id, name, start_date, end_date) 
VALUES (2, 'Workshop', '2024-06-15', '2024-06-17');
-- ✓ Success

-- Invalid: End date before start date
INSERT INTO events (id, name, start_date, end_date) 
VALUES (3, 'Seminar', '2024-07-01', '2024-06-30');
-- ✗ ERROR: new row for relation "events" violates check constraint

Example 2: Percentage Values

Discounts, tax rates, and completion percentages should be bounded:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    discount_percent NUMERIC(5, 2) CHECK (
        discount_percent >= 0 AND 
        discount_percent <= 100
    )
);

Test it:

-- Valid discounts
INSERT INTO products (id, name, price, discount_percent) 
VALUES 
    (1, 'Widget', 29.99, 10),      -- 10% off
    (2, 'Gadget', 49.99, 0),       -- No discount
    (3, 'Premium', 99.99, NULL);   -- Discount not set
-- ✓ All succeed

-- Invalid: Discount over 100%
INSERT INTO products (id, name, price, discount_percent) 
VALUES (4, 'Super Sale', 19.99, 150);
-- ✗ ERROR: violates check constraint

-- Invalid: Negative discount
INSERT INTO products (id, name, price, discount_percent) 
VALUES (5, 'Broken', 29.99, -20);
-- ✗ ERROR: violates check constraint

Example 3: Inventory Quantities

Stock levels should never be negative:

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0),
    CHECK (reserved <= quantity) -- Reserved can't exceed available
);

This creates two column-level constraints and one table-level constraint:

Test it:

-- Valid inventory
INSERT INTO inventory (product_id, quantity, reserved) 
VALUES (1, 100, 25);
-- ✓ Success: 100 units, 25 reserved, 75 available

-- Invalid: Negative quantity
INSERT INTO inventory (product_id, quantity, reserved) 
VALUES (2, -10, 0);
-- ✗ ERROR: violates check constraint "inventory_quantity_check"

-- Invalid: Reserved exceeds quantity
INSERT INTO inventory (product_id, quantity, reserved) 
VALUES (3, 50, 75);
-- ✗ ERROR: violates check constraint "inventory_check"

-- Invalid: Trying to reserve more than available
UPDATE inventory SET reserved = 150 WHERE product_id = 1;
-- ✗ ERROR: violates check constraint "inventory_check"

Example 4: Email Format (Basic)

While you should use proper email validation in your application, you can add a basic constraint:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

Note: This is a simple regex and won’t catch all invalid emails, but it prevents obviously wrong data:

-- Valid emails
INSERT INTO users (id, email) VALUES (1, '[email protected]');
INSERT INTO users (id, email) VALUES (2, '[email protected]');
-- ✓ Both succeed

-- Invalid emails
INSERT INTO users (id, email) VALUES (3, 'notanemail');
-- ✗ ERROR: violates check constraint

INSERT INTO users (id, email) VALUES (4, '@example.com');
-- ✗ ERROR: violates check constraint

INSERT INTO users (id, email) VALUES (5, 'user@');
-- ✗ ERROR: violates check constraint

Example 5: Multi-Column Business Rules

An order can’t have both a discount code AND a loyalty points redemption:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total NUMERIC(10, 2) NOT NULL CHECK (total >= 0),
    discount_code TEXT,
    loyalty_points_used INTEGER DEFAULT 0,
    CHECK (
        (discount_code IS NULL) OR 
        (loyalty_points_used = 0)
    )
);

This ensures mutual exclusivity:

-- Valid: Discount code only
INSERT INTO orders (id, total, discount_code, loyalty_points_used) 
VALUES (1, 99.99, 'SUMMER20', 0);
-- ✓ Success

-- Valid: Loyalty points only
INSERT INTO orders (id, total, discount_code, loyalty_points_used) 
VALUES (2, 149.99, NULL, 500);
-- ✓ Success

-- Valid: Neither
INSERT INTO orders (id, total, discount_code, loyalty_points_used) 
VALUES (3, 75.00, NULL, 0);
-- ✓ Success

-- Invalid: Both discount code AND loyalty points
INSERT INTO orders (id, total, discount_code, loyalty_points_used) 
VALUES (4, 199.99, 'VIP10', 1000);
-- ✗ ERROR: violates check constraint

Example 6: Conditional Requirements

A product must have dimensions if it’s shippable:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    is_shippable BOOLEAN NOT NULL DEFAULT true,
    weight_kg NUMERIC(8, 2),
    length_cm NUMERIC(8, 2),
    width_cm NUMERIC(8, 2),
    height_cm NUMERIC(8, 2),
    CHECK (
        NOT is_shippable OR (
            weight_kg > 0 AND 
            length_cm > 0 AND 
            width_cm > 0 AND 
            height_cm > 0
        )
    )
);

Test it:

-- Valid: Digital product (not shippable, no dimensions needed)
INSERT INTO products (id, name, is_shippable) 
VALUES (1, 'eBook', false);
-- ✓ Success

-- Valid: Physical product with all dimensions
INSERT INTO products (id, name, is_shippable, weight_kg, length_cm, width_cm, height_cm) 
VALUES (2, 'Widget', true, 0.5, 10, 8, 3);
-- ✓ Success

-- Invalid: Shippable product missing dimensions
INSERT INTO products (id, name, is_shippable, weight_kg) 
VALUES (3, 'Gadget', true, 1.2);
-- ✗ ERROR: violates check constraint

Advanced Patterns

Pattern 1: Named Constraints for Better Error Messages

Instead of generic constraint names, use descriptive ones:

ALTER TABLE products 
ADD CONSTRAINT products_price_must_be_positive 
CHECK (price > 0);

ALTER TABLE products 
ADD CONSTRAINT products_discount_must_be_percentage 
CHECK (discount_percent >= 0 AND discount_percent <= 100);

When a constraint fails, you’ll get:

ERROR: new row for relation "products" violates check constraint "products_price_must_be_positive"

Much better than:

ERROR: new row for relation "products" violates check constraint "products_check"

Pattern 2: Constraints with Explanatory Comments

Document your business rules directly in the schema:

ALTER TABLE orders 
ADD CONSTRAINT orders_minimum_amount 
CHECK (total >= 5.00);

COMMENT ON CONSTRAINT orders_minimum_amount ON orders IS 
'Minimum order amount is $5.00 to cover processing fees';

View comments:

SELECT 
    pg_get_constraintdef(oid) as definition,
    obj_description(oid) as comment
FROM pg_constraint 
WHERE conname = 'orders_minimum_amount';

Pattern 3: Combining CHECK with DEFAULT

Ensure sensible defaults that pass validation:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    priority INTEGER NOT NULL DEFAULT 5,
    CHECK (priority >= 1 AND priority <= 10),
    progress_percent INTEGER NOT NULL DEFAULT 0,
    CHECK (progress_percent >= 0 AND progress_percent <= 100)
);

Now inserts without explicit values still get validated:

-- Uses defaults, which pass constraints
INSERT INTO tasks (id, title) VALUES (1, 'Fix bug');
-- ✓ Success: priority=5, progress_percent=0

-- Can override defaults within constraints
INSERT INTO tasks (id, title, priority) VALUES (2, 'High priority', 9);
-- ✓ Success

-- Can't override with invalid values
INSERT INTO tasks (id, title, priority) VALUES (3, 'Invalid', 15);
-- ✗ ERROR: violates check constraint

Performance Considerations

CHECK Constraints are Fast

Unlike foreign keys or complex triggers, CHECK constraints have minimal overhead:

  • Evaluated during INSERT/UPDATE only (no SELECT penalty)
  • Simple expressions are extremely fast
  • No additional table lookups required

Benchmark Example

-- Create table with constraint
CREATE TABLE test_with_check (
    id INTEGER PRIMARY KEY,
    value INTEGER CHECK (value >= 0 AND value <= 1000)
);

-- Create table without constraint
CREATE TABLE test_without_check (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

-- Insert 1 million rows
-- WITH CHECK:    ~3.2 seconds
-- WITHOUT CHECK: ~3.0 seconds
-- Overhead: ~6% for guaranteed data integrity

The tiny performance cost is always worth the guarantee of data correctness.

Migration Strategy

Adding Constraints to Existing Tables

When adding constraints to tables with existing data, use NOT VALID to avoid scanning:

-- Step 1: Add constraint without validating existing rows
ALTER TABLE products 
ADD CONSTRAINT price_must_be_positive 
CHECK (price > 0) NOT VALID;

-- Step 2: Fix existing bad data
UPDATE products SET price = 0.01 WHERE price <= 0;

-- Step 3: Validate the constraint
ALTER TABLE products 
VALIDATE CONSTRAINT price_must_be_positive;

This approach:

  • Adds the constraint immediately for new writes
  • Doesn’t lock the table for a full scan
  • Gives you time to clean up bad data
  • Finally validates everything

Common Pitfalls

Pitfall 1: Forgetting NULL handling

-- ❌ WRONG: This allows NULL!
ALTER TABLE products 
ADD CONSTRAINT price_check 
CHECK (price > 0);

INSERT INTO products (name, price) VALUES ('Item', NULL);
-- ✓ Success (but probably not what you want)

-- ✅ CORRECT: Combine with NOT NULL
ALTER TABLE products 
ALTER COLUMN price SET NOT NULL;

ALTER TABLE products 
ADD CONSTRAINT price_check 
CHECK (price > 0);

Pitfall 2: Over-complex constraints

-- ❌ WRONG: Too complex, hard to debug
ALTER TABLE orders 
ADD CONSTRAINT complex_business_rule 
CHECK (
    (status = 'pending' AND paid_at IS NULL AND shipped_at IS NULL) OR
    (status = 'paid' AND paid_at IS NOT NULL AND shipped_at IS NULL) OR
    (status = 'shipped' AND paid_at IS NOT NULL AND shipped_at IS NOT NULL)
);

-- ✅ BETTER: Use multiple simple constraints
ALTER TABLE orders 
ADD CONSTRAINT paid_orders_have_payment_time 
CHECK (status != 'paid' OR paid_at IS NOT NULL);

ALTER TABLE orders 
ADD CONSTRAINT shipped_orders_have_ship_time 
CHECK (status != 'shipped' OR shipped_at IS NOT NULL);

ALTER TABLE orders 
ADD CONSTRAINT shipped_orders_must_be_paid 
CHECK (status != 'shipped' OR status = 'paid');

Pitfall 3: Constraints that should be triggers

-- ❌ WRONG: Can't reference other rows
ALTER TABLE inventory 
ADD CONSTRAINT low_stock_warning 
CHECK (quantity > 10);  -- Business decision, not data integrity

-- ✅ BETTER: Use a trigger for notifications
CREATE TRIGGER notify_low_stock 
AFTER UPDATE ON inventory
FOR EACH ROW
WHEN (NEW.quantity <= 10 AND NEW.quantity > 0)
EXECUTE FUNCTION send_low_stock_alert();

The Two-Layer Approach

Best practice: Defense in depth

  1. Application layer: Fast feedback, user-friendly error messages
  2. Database layer: Guaranteed enforcement, catches edge cases
# Layer 1: Application validation (user experience)
def create_product(name, price):
    # Immediate feedback, custom error message
    if price <= 0:
        raise ValidationError("Price must be positive. Did you mean $%.2f?" % abs(price))
    
    # If validation passes, proceed to database
    db.execute(
        "INSERT INTO products (name, price) VALUES (%s, %s)",
        (name, price)
    )

# Layer 2: Database constraint (guarantee)
# ALTER TABLE products ADD CONSTRAINT price_must_be_positive CHECK (price > 0);

This gives you:

  • Fast feedback for users (application layer)
  • Guaranteed correctness for all paths (database layer)
  • Clear error messages (application layer)
  • Ironclad protection against bugs, migrations, and direct DB access (database layer)

Summary

Key Takeaways:

  1. Always add CHECK constraints for business invariants
  2. Use descriptive constraint names
  3. Keep constraints simple and focused
  4. Combine CHECK with NOT NULL when appropriate
  5. Remember that CHECK constraints allow NULL unless combined with NOT NULL
  6. Use NOT VALID for adding constraints to existing tables
  7. Maintain application-level validation for user experience

Remember: CHECK constraints are not a replacement for application validation—they’re your last line of defense. They catch the bugs, the edge cases, and the things you didn’t think of. Treat PostgreSQL as a guarantor of your business invariants, not just a storage bucket.

Further Reading

Next in the series: Item 2 – Use citext for Case-Insensitive Identifiers

Leave a Comment