Constraints In Ddl - Learning Module

Loading content...

0/241

CHECK Constraint

Business Rules as Database Constraints

While PRIMARY KEY ensures identity, FOREIGN KEY ensures relationships, UNIQUE prevents duplicates, and NOT NULL requires presence, many business rules don't fit neatly into these categories:

"Order quantity must be positive"
"End date must be after start date"
"Status must be one of: pending, active, completed, cancelled"
"Discount percentage must be between 0 and 100"
"Email must contain an @ symbol"

The CHECK constraint provides the flexibility to express these arbitrary conditions directly in the database schema. It's the most versatile constraint, allowing any Boolean expression that the database can evaluate.

This transforms business logic from something enforced only in application code (where bugs can bypass it) to something guaranteed by the database itself.

What You Will Master

By the end of this page, you will understand how to write CHECK constraints for common business rules, navigate database-specific limitations, combine multiple conditions, and make informed decisions about when to use CHECK versus application-level validation.

CHECK Constraint Fundamentals

A CHECK constraint specifies a condition that must evaluate to TRUE (or UNKNOWN/NULL) for every row. If an INSERT or UPDATE would cause the condition to evaluate to FALSE, the operation is rejected.

Key Characteristics:

Boolean Expression: The constraint body is any SQL expression that evaluates to TRUE, FALSE, or NULL
Row-Level Evaluation: Each row is checked independently; CHECK cannot compare rows to each other
NULL Handling: If the condition evaluates to NULL (e.g., comparing with a NULL column), the constraint is considered satisfied (NULL is not FALSE)
Column or Table Level: Simple conditions can be inline; complex multi-column conditions require table-level syntax

Basic Syntax:

check_basic_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Column-level CHECK constraint
CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,
    product_name    VARCHAR(200) NOT NULL,
    unit_price      DECIMAL(10,2) CHECK (unit_price >= 0),     -- Price non-negative
    stock_quantity  INT CHECK (stock_quantity >= 0),            -- Quantity non-negative
    weight_kg       DECIMAL(8,3) CHECK (weight_kg > 0)          -- Weight positive
);
 
-- With explicit constraint names (recommended)
CREATE TABLE employees (
    employee_id     SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    age             INT CONSTRAINT chk_employee_age CHECK (age >= 18 AND age <= 100),
    salary          DECIMAL(12,2) CONSTRAINT chk_employee_salary CHECK (salary > 0)
);
 
 
-- Table-level CHECK constraint (required for multi-column conditions)
CREATE TABLE orders (
    order_id        SERIAL PRIMARY KEY,
    order_date      DATE NOT NULL,
    shipping_date   DATE,
    total_amount    DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    
    -- Shipping date must be on or after order date
    CONSTRAINT chk_orders_ship_after_order 
        CHECK (shipping_date IS NULL OR shipping_date >= order_date),
    
    -- Discount cannot exceed total
    CONSTRAINT chk_orders_discount_limit 
        CHECK (discount_amount >= 0 AND discount_amount <= total_amount)
);
 
 
-- Insert that succeeds
INSERT INTO products (product_name, unit_price, stock_quantity, weight_kg)
VALUES ('Widget', 29.99, 100, 0.5);
 
-- Insert that fails: negative price
INSERT INTO products (product_name, unit_price, stock_quantity, weight_kg)
VALUES ('Gadget', -5.00, 50, 0.3);
-- Error: new row violates check constraint "products_unit_price_check"

Always Name CHECK Constraints

Use chk_tablename_description naming pattern. When constraint violations occur, named constraints produce meaningful error messages: 'violates check constraint chk_orders_discount_limit' is far more useful than 'violates check constraint orders_check'.

Common CHECK Constraint Patterns

Certain business rules appear repeatedly across domains. Here are proven patterns for expressing them:

Pattern 1: Range Validation

check_range_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Numeric ranges
CREATE TABLE exam_scores (
    student_id      INT NOT NULL,
    exam_id         INT NOT NULL,
    score           INT NOT NULL 
        CONSTRAINT chk_score_range CHECK (score >= 0 AND score <= 100),
    PRIMARY KEY (student_id, exam_id)
);
 
-- Alternative using BETWEEN
CREATE TABLE exam_scores_v2 (
    student_id      INT NOT NULL,
    exam_id         INT NOT NULL,
    score           INT NOT NULL 
        CONSTRAINT chk_score_range CHECK (score BETWEEN 0 AND 100),
    PRIMARY KEY (student_id, exam_id)
);
 
-- Date ranges
CREATE TABLE contracts (
    contract_id     SERIAL PRIMARY KEY,
    client_id       INT NOT NULL,
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL,
    
    -- End must be after start
    CONSTRAINT chk_contract_date_range 
        CHECK (end_date > start_date),
    
    -- Contract must be at least 30 days
    CONSTRAINT chk_contract_min_duration 
        CHECK (end_date - start_date >= 30)
);
 
-- Percentage bounds
CREATE TABLE discounts (
    discount_id     SERIAL PRIMARY KEY,
    discount_pct    DECIMAL(5,2) NOT NULL
        CONSTRAINT chk_discount_pct CHECK (discount_pct >= 0 AND discount_pct <= 100)
);

Pattern 2: Enumerated Values

check_enum_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Status/type enumeration using CHECK
CREATE TABLE orders_status (
    order_id        SERIAL PRIMARY KEY,
    customer_id     INT NOT NULL,
    status          VARCHAR(20) NOT NULL 
        CONSTRAINT chk_order_status CHECK (
            status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')
        ),
    priority        CHAR(1) NOT NULL 
        CONSTRAINT chk_order_priority CHECK (priority IN ('L', 'M', 'H'))  -- Low/Medium/High
);
 
-- Payment methods
CREATE TABLE payments (
    payment_id      SERIAL PRIMARY KEY,
    order_id        INT NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    payment_method  VARCHAR(30) NOT NULL
        CONSTRAINT chk_payment_method CHECK (
            payment_method IN ('credit_card', 'debit_card', 'bank_transfer', 
                               'paypal', 'apple_pay', 'google_pay', 'cash')
        )
);
 
-- Boolean-like with meaningful names
CREATE TABLE subscriptions (
    subscription_id SERIAL PRIMARY KEY,
    user_id         INT NOT NULL,
    billing_cycle   VARCHAR(10) NOT NULL
        CONSTRAINT chk_billing_cycle CHECK (billing_cycle IN ('monthly', 'yearly')),
    auto_renew      VARCHAR(5) NOT NULL
        CONSTRAINT chk_auto_renew CHECK (auto_renew IN ('yes', 'no'))
);
 
 
-- Note: Many databases support ENUM types as alternative
-- PostgreSQL:
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
 
CREATE TABLE orders_pg (
    order_id        SERIAL PRIMARY KEY,
    status          order_status NOT NULL  -- Type-safe enumeration
);
 
-- MySQL:
-- CREATE TABLE orders_mysql (
--     order_id INT PRIMARY KEY,
--     status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL
-- );

Pattern 3: Conditional Logic

check_conditional_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- If A then B (implication: NOT A OR B)
CREATE TABLE employees_conditional (
    employee_id     SERIAL PRIMARY KEY,
    employee_type   VARCHAR(20) NOT NULL,
    hourly_rate     DECIMAL(10,2),
    annual_salary   DECIMAL(12,2),
    
    -- If contractor, must have hourly_rate
    CONSTRAINT chk_contractor_hourly 
        CHECK (employee_type != 'contractor' OR hourly_rate IS NOT NULL),
    
    -- If full-time, must have salary
    CONSTRAINT chk_fulltime_salary 
        CHECK (employee_type != 'full-time' OR annual_salary IS NOT NULL),
    
    -- Contractor cannot have salary; full-time cannot have hourly
    CONSTRAINT chk_pay_type_exclusive 
        CHECK (
            (employee_type = 'contractor' AND hourly_rate IS NOT NULL AND annual_salary IS NULL)
            OR (employee_type = 'full-time' AND annual_salary IS NOT NULL AND hourly_rate IS NULL)
            OR (employee_type = 'part-time')  -- Part-time can have either
        )
);
 
 
-- Nullable field only valid under certain conditions
CREATE TABLE shipments (
    shipment_id     SERIAL PRIMARY KEY,
    order_id        INT NOT NULL,
    status          VARCHAR(20) NOT NULL,
    shipped_date    DATE,
    delivered_date  DATE,
    
    -- shipped_date required when status is 'shipped' or 'delivered'
    CONSTRAINT chk_shipped_date_if_shipped 
        CHECK (status NOT IN ('shipped', 'delivered') OR shipped_date IS NOT NULL),
    
    -- delivered_date required when status is 'delivered'
    CONSTRAINT chk_delivered_date_if_delivered 
        CHECK (status != 'delivered' OR delivered_date IS NOT NULL),
    
    -- delivered_date must be >= shipped_date
    CONSTRAINT chk_delivery_after_ship 
        CHECK (delivered_date IS NULL OR delivered_date >= shipped_date)
);

Pattern 4: Format Validation

check_format_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Basic format validation (limited without regex)
CREATE TABLE contacts (
    contact_id      SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL
        CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%'),  -- Basic @ and . check
    phone           VARCHAR(20)
        CONSTRAINT chk_phone_format CHECK (
            phone IS NULL OR (LENGTH(phone) >= 10 AND phone NOT LIKE '%[^0-9-() ]%')
        )
);
 
 
-- PostgreSQL: Full regex support with ~ operator
CREATE TABLE accounts_pg (
    account_id      SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL
        CONSTRAINT chk_email_regex CHECK (
            email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$'
        ),
    phone           VARCHAR(20)
        CONSTRAINT chk_phone_regex CHECK (
            phone IS NULL OR phone ~ '^+?[0-9]{10,15}$'
        ),
    postal_code     VARCHAR(10)
        CONSTRAINT chk_us_postal CHECK (
            postal_code ~ '^d{5}(-d{4})?$'  -- 12345 or 12345-6789
        )
);
 
 
-- Length constraints
CREATE TABLE products_format (
    product_id      SERIAL PRIMARY KEY,
    sku             VARCHAR(20) NOT NULL
        CONSTRAINT chk_sku_format CHECK (
            LENGTH(sku) >= 5 AND LENGTH(sku) <= 15 AND sku NOT LIKE '% %'
        ),
    description     TEXT
        CONSTRAINT chk_desc_length CHECK (
            description IS NULL OR LENGTH(description) <= 10000
        )
);

Format Validation Trade-offs

Complex format validation (email regex, phone number parsing) is often better handled at the application layer where validation libraries are more powerful and error messages more customizable. Use CHECK for fundamental constraints; use application validation for nuanced format rules.

Multi-Column CHECK Constraints

Some business rules involve relationships between multiple columns. These require table-level CHECK constraints that reference multiple columns in a single condition.

Date Ordering:

check_multi_column.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- Multiple date columns with ordering requirements
CREATE TABLE projects (
    project_id      SERIAL PRIMARY KEY,
    project_name    VARCHAR(200) NOT NULL,
    planned_start   DATE NOT NULL,
    planned_end     DATE NOT NULL,
    actual_start    DATE,
    actual_end      DATE,
    
    -- Planned dates must be in order
    CONSTRAINT chk_planned_dates 
        CHECK (planned_end >= planned_start),
    
    -- Actual dates in order (when present)
    CONSTRAINT chk_actual_dates 
        CHECK (actual_end IS NULL OR actual_start IS NULL OR actual_end >= actual_start),
    
    -- Actual start can't be before planned start (allow some flexibility)
    CONSTRAINT chk_start_not_before_planned 
        CHECK (actual_start IS NULL OR actual_start >= planned_start - INTERVAL '7 days')
);
 
-- Financial constraints across columns
CREATE TABLE invoices (
    invoice_id      SERIAL PRIMARY KEY,
    subtotal        DECIMAL(12,2) NOT NULL,
    tax_amount      DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount        DECIMAL(12,2) NOT NULL DEFAULT 0,
    total           DECIMAL(12,2) NOT NULL,
    amount_paid     DECIMAL(12,2) NOT NULL DEFAULT 0,
    
    -- Amounts are non-negative
    CONSTRAINT chk_amounts_non_negative 
        CHECK (subtotal >= 0 AND tax_amount >= 0 AND discount >= 0 AND total >= 0 AND amount_paid >= 0),
    
    -- Total should equal subtotal + tax - discount
    CONSTRAINT chk_total_calculation 
        CHECK (total = subtotal + tax_amount - discount),
    
    -- Can't pay more than total
    CONSTRAINT chk_payment_not_exceed 
        CHECK (amount_paid <= total),
    
    -- Discount can't exceed subtotal
    CONSTRAINT chk_discount_limit 
        CHECK (discount <= subtotal)
);
 
-- Quantity and amount consistency
CREATE TABLE order_items (
    item_id         SERIAL PRIMARY KEY,
    order_id        INT NOT NULL,
    product_id      INT NOT NULL,
    unit_price      DECIMAL(10,2) NOT NULL,
    quantity        INT NOT NULL,
    line_total      DECIMAL(12,2) NOT NULL,
    
    -- Quantity must be positive
    CONSTRAINT chk_quantity_positive CHECK (quantity > 0),
    
    -- Unit price non-negative
    CONSTRAINT chk_unit_price_non_negative CHECK (unit_price >= 0),
    
    -- Line total must equal unit_price * quantity
    CONSTRAINT chk_line_total_calculation 
        CHECK (line_total = unit_price * quantity)
);

Mutually Exclusive Options:

check_mutually_exclusive.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Exactly one of multiple options must be provided
CREATE TABLE notifications (
    notification_id SERIAL PRIMARY KEY,
    user_id         INT NOT NULL,
    message         TEXT NOT NULL,
    -- Notification can be sent via email, SMS, or push (exactly one)
    email_address   VARCHAR(255),
    phone_number    VARCHAR(20),
    device_token    VARCHAR(255),
    
    -- Exactly one delivery method must be specified
    CONSTRAINT chk_single_delivery_method CHECK (
        (email_address IS NOT NULL AND phone_number IS NULL AND device_token IS NULL) OR
        (email_address IS NULL AND phone_number IS NOT NULL AND device_token IS NULL) OR
        (email_address IS NULL AND phone_number IS NULL AND device_token IS NOT NULL)
    )
);
 
-- Alternative: Using a count approach (cleaner for many options)
-- PostgreSQL:
ALTER TABLE notifications DROP CONSTRAINT chk_single_delivery_method;
ALTER TABLE notifications ADD CONSTRAINT chk_single_delivery_method CHECK (
    (CASE WHEN email_address IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN phone_number IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN device_token IS NOT NULL THEN 1 ELSE 0 END) = 1
);
 
 
-- At least one of multiple options (not exactly one)
CREATE TABLE contacts_multi (
    contact_id      SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(255),
    phone           VARCHAR(20),
    address         TEXT,
    
    -- At least one contact method required
    CONSTRAINT chk_at_least_one_contact CHECK (
        email IS NOT NULL OR phone IS NOT NULL OR address IS NOT NULL
    )
);

Keep CHECK Logic Simple

If a single CHECK constraint becomes a long boolean expression with many conditions, consider: (1) splitting into multiple named constraints, (2) redesigning the table to avoid the complex rule, or (3) moving complex validation to application layer with clear documentation.

CHECK Constraint Limitations

CHECK constraints are powerful but have important limitations you must understand:

1. Row-Level Only (No Queries):

Standard CHECK constraints cannot reference other rows or other tables. Each row is evaluated in isolation.

check_limitations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- INVALID: Cannot reference other tables
CREATE TABLE orders_invalid (
    order_id        SERIAL PRIMARY KEY,
    customer_id     INT NOT NULL,
    order_total     DECIMAL(12,2) NOT NULL,
    
    -- THIS DOES NOT WORK:
    -- CONSTRAINT chk_customer_exists CHECK (
    --     customer_id IN (SELECT customer_id FROM customers)
    -- )
    -- Error: cannot use subquery in check constraint
);
 
-- INVALID: Cannot reference other rows
CREATE TABLE accounts_invalid (
    account_id      SERIAL PRIMARY KEY,
    account_type    VARCHAR(20) NOT NULL,
    balance         DECIMAL(15,2) NOT NULL,
    
    -- THIS DOES NOT WORK:
    -- CONSTRAINT chk_total_balance CHECK (
    --     (SELECT SUM(balance) FROM accounts) <= 1000000
    -- )
    -- Error: cannot use subquery in check constraint
);
 
-- For cross-row or cross-table validation, use:
-- 1. Foreign key constraints (for existence checks)
-- 2. Triggers (for complex cross-row logic)
-- 3. Stored procedures (for complex validation)
-- 4. Application-level validation
 
-- PostgreSQL workaround: Use triggers for complex validation
CREATE OR REPLACE FUNCTION validate_customer_credit_limit()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.order_total > (SELECT credit_limit FROM customers WHERE customer_id = NEW.customer_id) THEN
        RAISE EXCEPTION 'Order total exceeds customer credit limit';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_validate_credit
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION validate_customer_credit_limit();

2. Database-Specific Functions:

CHECK constraints can only use functions and operators that the database allows in CHECK contexts. Some databases restrict what can appear in a CHECK constraint.

CHECK Constraint Feature Support by Database
Feature	PostgreSQL	MySQL	SQL Server	Oracle
Basic comparisons	✓	✓	✓	✓
IN lists	✓	✓	✓	✓
BETWEEN	✓	✓	✓	✓
LIKE patterns	✓	✓	✓	✓
Regular expressions	✓ (~ operator)	✓ (REGEXP)	✗	✓ (REGEXP_LIKE)
Subqueries	✗	✗	✗	✗
Current timestamp	✓	✗ (pre-8.0.16)	✓	✓
User-defined functions	✓ (immutable)	✗	✗	✓ (deterministic)

3. MySQL Historical Limitation:

Before MySQL 8.0.16, CHECK constraints were parsed but not enforced! This was a notorious trap that caught many developers.

mysql_check_history.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- MySQL version check for CHECK support
 
-- MySQL 8.0.16+: CHECK constraints are enforced
SELECT VERSION();  -- Must be 8.0.16 or higher
 
-- Pre-8.0.16 behavior (silent ignore):
CREATE TABLE pre_8016 (
    id INT PRIMARY KEY,
    score INT CHECK (score >= 0 AND score <= 100)  -- Parsed but IGNORED
);
 
INSERT INTO pre_8016 VALUES (1, 500);  -- Succeeds! No validation!
 
-- If you need CHECK on older MySQL, use triggers:
CREATE TRIGGER trg_check_score
BEFORE INSERT ON scores
FOR EACH ROW
BEGIN
    IF NEW.score < 0 OR NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Score must be between 0 and 100';
    END IF;
END;

Verify CHECK Enforcement on Your Database

Before relying on CHECK constraints, verify they're actually enforced in your database version. Insert a violating row and confirm it fails. This is especially important when migrating schemas between database systems.

Managing CHECK Constraints

Production systems require adding, modifying, and removing CHECK constraints over time.

Adding CHECK Constraints:

manage_check_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Adding a CHECK constraint to existing table
 
-- First, verify existing data satisfies the constraint
SELECT * FROM products WHERE unit_price < 0;
 
-- If violations exist, fix them first
UPDATE products SET unit_price = 0 WHERE unit_price < 0;
 
-- Then add the constraint
ALTER TABLE products
ADD CONSTRAINT chk_products_price_non_negative 
CHECK (unit_price >= 0);
 
 
-- PostgreSQL: Add constraint without validating existing data (fast)
-- New inserts/updates will be validated; existing data trusted
ALTER TABLE products
ADD CONSTRAINT chk_products_price_new 
CHECK (unit_price >= 0.01) NOT VALID;
 
-- Later, validate existing data (can run concurrently)
ALTER TABLE products 
VALIDATE CONSTRAINT chk_products_price_new;
 
 
-- Dropping a CHECK constraint
ALTER TABLE products
DROP CONSTRAINT chk_products_price_non_negative;
 
 
-- Modifying a CHECK constraint (must drop and recreate)
-- There is no ALTER CONSTRAINT ... MODIFY
 
-- Step 1: Drop old constraint
ALTER TABLE orders 
DROP CONSTRAINT chk_order_status;
 
-- Step 2: Add new constraint with updated condition
ALTER TABLE orders
ADD CONSTRAINT chk_order_status CHECK (
    status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'returned')
);
 
 
-- Disabling CHECK constraints temporarily (SQL Server)
ALTER TABLE products NOCHECK CONSTRAINT chk_products_price_non_negative;
 
-- Re-enabling
ALTER TABLE products CHECK CONSTRAINT chk_products_price_non_negative;
 
-- Re-enable and validate existing data
ALTER TABLE products WITH CHECK CHECK CONSTRAINT chk_products_price_non_negative;

Viewing CHECK Constraints:

view_check_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- PostgreSQL: View all CHECK constraints
SELECT 
    tc.table_name,
    tc.constraint_name,
    cc.check_clause
FROM information_schema.table_constraints tc
JOIN information_schema.check_constraints cc 
    ON tc.constraint_name = cc.constraint_name
WHERE tc.constraint_type = 'CHECK'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name, tc.constraint_name;
 
 
-- SQL Server: View CHECK constraints
SELECT 
    t.name AS table_name,
    c.name AS constraint_name,
    c.definition
FROM sys.check_constraints c
JOIN sys.tables t ON c.parent_object_id = t.object_id
ORDER BY t.name, c.name;
 
 
-- MySQL 8.0+: View CHECK constraints
SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();
 
 
-- Oracle: View CHECK constraints
SELECT 
    table_name,
    constraint_name,
    search_condition
FROM user_constraints
WHERE constraint_type = 'C'
ORDER BY table_name, constraint_name;

CHECK vs Application Validation

Both database CHECK constraints and application-level validation have their place. Understanding when to use each is key to robust system design.

Comparison:

CHECK Constraints vs Application Validation
Aspect	CHECK Constraint	Application Validation
Enforcement Location	Database (centralized)	Application code (distributed)
Bypass Risk	Cannot be bypassed	Can be bypassed (bugs, direct DB access)
Error Messages	Generic (constraint name)	Customizable, user-friendly
Complexity Limit	Single-row, no queries	Unlimited (can query, call APIs)
Performance	Very fast (compiled)	Additional code execution
Internationalization	No i18n support	Full i18n for errors
Modification	Schema migration required	Code deployment
Testing	Integration tests needed	Unit testable

When to Use CHECK Constraints:

Prefer CHECK Constraints For

•Data Integrity Invariants: Rules that must NEVER be violated (positive quantities, valid date ranges)
•Simple Value Constraints: Numeric ranges, enumerated values, basic format checks
•Cross-Column Consistency: end_date >= start_date, total = sum of parts
•Defense in Depth: Add CHECK even if app validates—prevents bugs from corrupting data
•Multiple Access Points: When data can be modified through multiple apps, APIs, or direct access

Prefer Application Validation For

•User-Facing Error Messages: 'Please enter a valid email' vs 'chk_email_format violation'
•Complex Business Logic: Rules involving multiple tables, external services, or temporal context
•Frequently Changing Rules: Validation that changes often (don't want schema migrations)
•Format Validation: Sophisticated email/phone/address parsing with good error messages
•Conditional Validation: Rules that depend on user roles, feature flags, or request context

layered_validation.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
// Best Practice: Layered validation approach
 
// Layer 1: Application validation (user-friendly, fast feedback)
function validateOrder(order: OrderInput): ValidationResult {
    const errors: string[] = [];
    
    if (order.quantity <= 0) {
        errors.push('Quantity must be at least 1');
    }
    if (order.totalPrice < 0) {
        errors.push('Total price cannot be negative');
    }
    if (order.shippingDate && order.orderDate && 
        order.shippingDate < order.orderDate) {
        errors.push('Shipping date cannot be before order date');
    }
    
    return { valid: errors.length === 0, errors };
}
 
// Layer 2: Database CHECK constraints (safety net)
/*
CREATE TABLE orders (
    order_id        SERIAL PRIMARY KEY,
    quantity        INT NOT NULL 
        CONSTRAINT chk_quantity_positive CHECK (quantity > 0),
    total_price     DECIMAL(12,2) NOT NULL 
        CONSTRAINT chk_price_non_negative CHECK (total_price >= 0),
    order_date      DATE NOT NULL,
    shipping_date   DATE,
    CONSTRAINT chk_ship_after_order 
        CHECK (shipping_date IS NULL OR shipping_date >= order_date)
);
*/
 
// Controller: Both layers work together
async function createOrder(req: Request) {
    // First: Application validation (good errors)
    const validation = validateOrder(req.body);
    if (!validation.valid) {
        return { status: 400, errors: validation.errors };
    }
    
    // Then: Database insert (CHECK is safety net)
    try {
        await db.orders.create({ data: req.body });
    } catch (error) {
        if (isCheckConstraintViolation(error)) {
            // If we reach here, app validation has a bug!
            // Log for investigation; return generic error
            logger.error('CHECK violation despite app validation', { error, order: req.body });
            return { status: 500, errors: ['Validation error'] };
        }
        throw error;
    }
}

Defense in Depth

Use CHECK constraints AND application validation. Application validation provides good UX; database constraints prevent corruption when application code has bugs. If both validate the same rule, the CHECK constraint is your safety net that never fails.

Advanced CHECK Techniques

Beyond basic patterns, CHECK constraints can implement sophisticated validation logic in databases that support advanced features.

PostgreSQL: Using CASE Expressions:

advanced_check_postgres.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Complex conditional validation with CASE
CREATE TABLE insurance_policies (
    policy_id       SERIAL PRIMARY KEY,
    policy_type     VARCHAR(30) NOT NULL,
    coverage_amount DECIMAL(12,2) NOT NULL,
    deductible      DECIMAL(10,2) NOT NULL,
    premium         DECIMAL(10,2) NOT NULL,
    
    -- Different limits based on policy type
    CONSTRAINT chk_coverage_by_type CHECK (
        CASE policy_type
            WHEN 'basic' THEN coverage_amount <= 100000
            WHEN 'standard' THEN coverage_amount <= 500000
            WHEN 'premium' THEN coverage_amount <= 2000000
            ELSE FALSE  -- Unknown policy type fails
        END
    ),
    
    -- Deductible must be appropriate ratio of coverage
    CONSTRAINT chk_deductible_ratio CHECK (
        deductible >= coverage_amount * 0.01 AND 
        deductible <= coverage_amount * 0.20
    )
);
 
 
-- PostgreSQL: CHECK with array operators
CREATE TABLE products_postgres (
    product_id      SERIAL PRIMARY KEY,
    product_name    VARCHAR(200) NOT NULL,
    tags            TEXT[] NOT NULL DEFAULT '{}',
    category        VARCHAR(50) NOT NULL,
    
    -- Category must be from allowed list
    CONSTRAINT chk_valid_category CHECK (
        category = ANY(ARRAY['electronics', 'clothing', 'food', 'furniture', 'other'])
    ),
    
    -- Limit number of tags
    CONSTRAINT chk_max_tags CHECK (array_length(tags, 1) <= 10 OR tags = '{}')
);
 
 
-- PostgreSQL: CHECK with IMMUTABLE functions
CREATE OR REPLACE FUNCTION is_valid_isbn(isbn TEXT) 
RETURNS BOOLEAN AS $$
BEGIN
    -- Simplified ISBN-10 check
    RETURN LENGTH(REGEXP_REPLACE(isbn, '[^0-9X]', '', 'g')) = 10;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
 
CREATE TABLE books (
    book_id         SERIAL PRIMARY KEY,
    title           VARCHAR(500) NOT NULL,
    isbn            CHAR(13) NOT NULL,
    
    -- Use custom function in CHECK
    CONSTRAINT chk_valid_isbn CHECK (is_valid_isbn(isbn))
);

Domain Types (PostgreSQL):

For reusable constraints, PostgreSQL supports DOMAIN types—custom types with built-in CHECK constraints.

domain_types.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- PostgreSQL: DOMAIN types for reusable constraints
 
-- Domain for positive integers
CREATE DOMAIN positive_int AS INT
    CHECK (VALUE > 0);
 
-- Domain for percentage (0-100)
CREATE DOMAIN percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Domain for US ZIP code
CREATE DOMAIN us_zipcode AS VARCHAR(10)
    CHECK (VALUE ~ '^d{5}(-d{4})?$');
 
-- Domain for email addresses
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$');
 
-- Domain for currency amount
CREATE DOMAIN currency AS DECIMAL(15,2)
    CHECK (VALUE >= 0);
 
 
-- Using domains in table definitions
CREATE TABLE orders_with_domains (
    order_id        SERIAL PRIMARY KEY,
    quantity        positive_int NOT NULL,         -- Inherits positive check
    discount_pct    percentage DEFAULT 0,          -- Inherits 0-100 range
    shipping_zip    us_zipcode NOT NULL,           -- Inherits format check
    contact_email   email_address NOT NULL,        -- Inherits email format
    total_amount    currency NOT NULL              -- Inherits non-negative
);
 
-- All CHECK constraints from domains are automatically applied
INSERT INTO orders_with_domains (quantity, discount_pct, shipping_zip, contact_email, total_amount)
VALUES (5, 15.5, '12345', 'customer@example.com', 199.99);  -- OK
 
INSERT INTO orders_with_domains (quantity, discount_pct, shipping_zip, contact_email, total_amount)
VALUES (-1, 15.5, '12345', 'customer@example.com', 199.99);  -- Fails: quantity not positive
 
 
-- Modifying a domain affects all tables using it
ALTER DOMAIN percentage
ADD CONSTRAINT percentage_max_50 CHECK (VALUE <= 50);  -- Now max is 50%

Domains Are Powerful but PostgreSQL-Specific

Domains provide excellent code reuse and centralized constraint management, but they're PostgreSQL-specific. If database portability matters, use table-level CHECK constraints or application validation instead.

Summary: CHECK Constraint Mastery

We've explored CHECK constraints comprehensively—from basic patterns through advanced techniques to practical deployment considerations. Let's consolidate the essential knowledge:

Key Takeaways

•Arbitrary Boolean Conditions: CHECK enforces any expression that evaluates to TRUE, FALSE, or NULL for each row.
•Row-Level Only: Cannot reference other rows or tables; use triggers or application logic for cross-row validation.
•Common Patterns: Range validation, enumerated values, conditional logic, format checks, multi-column consistency.
•Always Name Constraints: Use chk_tablename_rule naming for debuggable error messages.
•NULL Is Not FALSE: CHECK passes when condition is NULL (e.g., nullable column in comparison). Use NOT NULL + CHECK for strict validation.
•Database Differences: MySQL < 8.0.16 ignores CHECK; regex support varies; test on your target database.
•Layered Validation: Use CHECK for invariants + application validation for UX. CHECK is your safety net.
•PostgreSQL Advanced Features: Domains, custom immutable functions, array operators extend CHECK capabilities significantly.

Module Complete:

You've now mastered all five fundamental DDL constraints:

PRIMARY KEY: Entity identity and uniqueness
FOREIGN KEY: Referential integrity between tables
UNIQUE: Alternate keys and business uniqueness rules
NOT NULL: Mandatory value enforcement
CHECK: Arbitrary business rule validation

Together, these constraints transform a database from a mere data container into a robust system that actively guards data integrity. Proper constraint usage prevents bugs, simplifies application code, and ensures data quality regardless of how data enters the system.

Module Complete

You now have comprehensive mastery of all DDL constraints. This knowledge enables you to design database schemas that enforce data integrity at the most fundamental level—the storage layer itself. Your databases will prevent more bugs, require less application validation code, and maintain higher data quality.