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

5. Internal Architecture

6. Visual Explanation

7. Practical Example

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?

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.