ReviseAlgo Logo

Keys & Constraints

CHECK, DEFAULT, and NOT NULL Constraints

Validating column inputs directly at the schema layer.

Last Updated: June 15, 2026 • 15 min read

1. Introduction

Beyond primary and foreign keys, PostgreSQL provides CHECK, DEFAULT, and NOT NULL constraints to enforce data quality at the schema level. These constraints validate data before it enters the database, preventing invalid states that application-level validation might miss.

2. Why It Matters

  • Defense in depth: Application validation can be bypassed (direct SQL access, API bugs, migration scripts). Database constraints are the last line of defense.
  • Data quality guarantees: CHECK constraints ensure prices are non-negative, emails contain '@', dates are in valid ranges — impossible to violate regardless of how data is inserted.
  • Reduced application code: DEFAULT values eliminate the need to set timestamps, counters, and status fields in every INSERT statement.
  • Schema as documentation: Constraints make business rules visible in the schema itself — any developer can understand valid data ranges.

3. Real-World Analogy

A bank vault has multiple security layers: guard at the entrance (application validation), keycard reader (API authentication), and a time-lock on the vault itself (database constraint). Even if someone bypasses the guard and keycard, the time-lock prevents access outside authorized hours. CHECK, DEFAULT, and NOT NULL are the database's time-lock.

4. How It Works

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,                          -- cannot be NULL
    price NUMERIC(10,2) NOT NULL DEFAULT 0.00,   -- NOT NULL + DEFAULT
    stock INT DEFAULT 0 CHECK (stock >= 0),      -- CHECK constraint
    status TEXT DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'discontinued')),
    email TEXT CHECK (email LIKE '%@%.%'),       -- simple email check
    discount NUMERIC(5,2) CHECK (discount >= 0 AND discount <= 100),
    created_at TIMESTAMP DEFAULT NOW(),
    sku TEXT,
    CONSTRAINT unique_sku UNIQUE (sku)
);

-- Table-level CHECK (can reference multiple columns)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CHECK (end_date > start_date),               -- cross-column check
    CHECK (start_date >= CURRENT_DATE)            -- future events only
);


-- Adding constraints to existing tables
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 13);
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

5. Internal Architecture

Constraint Enforcement Order (on INSERT/UPDATE):
┌─────────────────────────────────────────────────────┐
│ 1. NOT NULL check (column cannot be NULL)            │
│ 2. CHECK constraints (evaluate boolean expression)   │
│ 3. UNIQUE / PRIMARY KEY (check for duplicates)       │
│ 4. FOREIGN KEY (check referential integrity)         │
│ If ANY check fails → entire row is rejected          │
│ All checks run BEFORE the row is written to disk     │
└─────────────────────────────────────────────────────┘

Performance:
  - NOT NULL: O(1) — simple null bitmap check
  - CHECK: O(1) — expression evaluation
  - UNIQUE: O(log N) — index lookup
  - FK: O(log N) — index lookup on parent table
  All are extremely fast with proper indexing.

6. Visual Explanation

7. Practical Example

-- E-commerce schema with comprehensive constraints
CREATE TABLE coupons (
    code TEXT PRIMARY KEY,
    discount_pct NUMERIC(5,2) NOT NULL
        CHECK (discount_pct > 0 AND discount_pct <= 100),
    max_uses INT NOT NULL CHECK (max_uses > 0),
    current_uses INT NOT NULL DEFAULT 0 CHECK (current_uses >= 0),
    valid_from TIMESTAMP NOT NULL DEFAULT NOW(),
    valid_until TIMESTAMP NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    CHECK (valid_until > valid_from),
    CHECK (current_uses <= max_uses)
);

-- This INSERT succeeds:
INSERT INTO coupons (code, discount_pct, max_uses, valid_until)
VALUES ('SAVE20', 20.00, 100, '2026-12-31');


-- This INSERT fails (CHECK violation):
INSERT INTO coupons (code, discount_pct, max_uses, valid_until)
VALUES ('BAD', 150.00, 100, '2026-12-31');
-- ERROR: new row violates check constraint "coupons_discount_pct_check"

8. Common Mistakes

Adding NOT NULL to existing tables with NULL data

ALTER TABLE users ALTER COLUMN phone SET NOT NULL fails if any existing row has a NULL phone. Fix NULLs first: UPDATE users SET phone = '' WHERE phone IS NULL.

Over-constraining with CHECK

Too-restrictive CHECK constraints prevent legitimate data changes. A CHECK on status IN ('active', 'inactive') breaks when you need to add 'suspended'. Use application-level validation for business rules that change frequently.

Interview Insight

"When should validation be in the application vs the database?" — Both. Application validation provides user-friendly error messages. Database constraints guarantee integrity regardless of access path. Never rely solely on application validation.

9. Quick Quiz

Q1: Can a CHECK constraint reference columns from another table?

Answer: No. CHECK constraints can only reference columns in the same table. For cross-table validation, use foreign keys, triggers, or application-level logic.

10. Scenario-Based Challenge

Challenge: Booking System Constraints

Design a hotel room booking table with these constraints:

  1. check_in must be before check_out (cross-column CHECK).
  2. guests must be between 1 and room capacity (use a trigger for dynamic capacity check).
  3. status defaults to 'confirmed' and must be one of: confirmed, cancelled, completed.
  4. NOT NULL on all critical fields, DEFAULT NOW() on created_at.

11. Debugging Exercise

This constraint addition fails. Why?

-- Table has 100K rows, some with NULL email
ALTER TABLE users ADD CONSTRAINT check_email CHECK (email LIKE '%@%');

Issue:

  1. CHECK constraints are validated against ALL existing rows when added. NULL LIKE '%@%' returns UNKNOWN (not TRUE), causing the constraint to fail.
  2. Fix: Either update NULLs first or add the constraint with NOT VALID: ALTER TABLE users ADD CONSTRAINT check_email CHECK (email IS NULL OR email LIKE '%@%') NOT VALID, then VALIDATE later.

12. Interview Questions

Q1: How do you add a NOT NULL constraint to a large table without downtime?

A: (1) Add the column as nullable with a DEFAULT value, (2) backfill existing rows in batches, (3) SET NOT NULL after all rows have values. PostgreSQL 11+ optimizes adding columns with non-volatile DEFAULTs — it doesn't rewrite the table.

Q2: What's the difference between DEFAULT and GENERATED columns?

A: DEFAULT sets a value only when the column is omitted from INSERT. GENERATED ALWAYS columns are computed automatically on every INSERT/UPDATE based on an expression. Generated columns are always in sync; DEFAULT values are set once.

13. Production Considerations

  • NOT VALID for large tables: Add constraints with NOT VALID first (instant), then VALIDATE CONSTRAINT separately (scans table but doesn't block writes).
  • Constraint naming: Name constraints explicitly: CONSTRAINT chk_price_positive CHECK (price > 0). Auto-generated names are hard to reference when modifying constraints.
  • DEFAULT NOW() vs application timestamps: Database DEFAULT NOW() uses the DB server clock. If app and DB clocks drift, timestamps differ. Use NTP sync or pass timestamps from the application.
  • Immutability patterns: For columns that should never change (like created_at), use a trigger to prevent UPDATE: IF NEW.created_at != OLD.created_at THEN RAISE EXCEPTION.