PostgreSQL Constraints and Validation: CHECK, EXCLUDE, Deferrable Foreign Keys, and Domain Types

Application-level validation can be bypassed — by direct database access, migrations, batch scripts, or bugs. Database constraints cannot. PostgreSQL's constraint system goes far beyond NOT NULL: CHECK validates business rules, EXCLUDE prevents overlapping ranges (perfect for bookings and reservations), deferrable constraints handle circular references, and domain types let you define reusable validation logic once.

CHECK Constraints

-- Basic CHECK: single column rule
CREATE TABLE products (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents > 0),
  tax_rate    NUMERIC(5, 4) NOT NULL
    CHECK (tax_rate >= 0 AND tax_rate <= 1),   -- 0-100%

  -- Named constraint: error message includes constraint name
  discount_pct NUMERIC(5, 2) NOT NULL DEFAULT 0
    CONSTRAINT chk_discount_valid CHECK (discount_pct >= 0 AND discount_pct <= 100)
);

-- Multi-column CHECK: cross-column rules
CREATE TABLE subscriptions (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  starts_at    TIMESTAMPTZ NOT NULL,
  ends_at      TIMESTAMPTZ,

  -- ends_at must be after starts_at if provided
  CONSTRAINT chk_subscription_dates
    CHECK (ends_at IS NULL OR ends_at > starts_at),

  plan         TEXT NOT NULL CHECK (plan IN ('free', 'starter', 'growth', 'enterprise')),
  mrr_cents    INTEGER NOT NULL DEFAULT 0,

  -- Free plan can't have MRR
  CONSTRAINT chk_free_plan_no_mrr
    CHECK (plan != 'free' OR mrr_cents = 0)
);

-- Computed column validation
CREATE TABLE invoices (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subtotal_cents  INTEGER NOT NULL CHECK (subtotal_cents >= 0),
  tax_cents       INTEGER NOT NULL CHECK (tax_cents >= 0),
  total_cents     INTEGER NOT NULL,

  -- Total must equal subtotal + tax
  CONSTRAINT chk_invoice_total_correct
    CHECK (total_cents = subtotal_cents + tax_cents)
);

EXCLUDE Constraints: Non-Overlapping Ranges

EXCLUDE is PostgreSQL's most powerful constraint — it prevents overlap between rows using GiST index operators. Essential for booking/reservation systems:

-- Enable btree_gist for EXCLUDE with non-range types
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Resource bookings: prevent double-booking
CREATE TABLE bookings (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL REFERENCES resources(id),
  user_id     UUID NOT NULL REFERENCES users(id),
  booked_for  TSTZRANGE NOT NULL,  -- e.g., [2027-05-21 09:00, 2027-05-21 10:00)

  -- Prevent overlapping bookings for the same resource
  -- Two ranges overlap if they share any time
  CONSTRAINT excl_booking_no_overlap
    EXCLUDE USING GIST (
      resource_id WITH =,      -- Same resource
      booked_for  WITH &&      -- Overlapping time ranges
    )
);

-- Insert a booking
INSERT INTO bookings (resource_id, user_id, booked_for)
VALUES (
  'resource-uuid',
  'user-uuid',
  '[2027-05-21 09:00+00, 2027-05-21 10:00+00)'
);

-- This insert fails with "conflicting key value violates exclusion constraint":
INSERT INTO bookings (resource_id, user_id, booked_for)
VALUES (
  'resource-uuid',
  'user-uuid-2',
  '[2027-05-21 09:30+00, 2027-05-21 11:00+00)'  -- Overlaps with existing
);

-- Query: find available slots (what's NOT booked)
SELECT
  generate_series(
    '2027-05-21 08:00+00'::timestamptz,
    '2027-05-21 18:00+00'::timestamptz,
    '30 minutes'::interval
  ) AS slot_start
EXCEPT
SELECT lower(booked_for)
FROM bookings
WHERE resource_id = 'resource-uuid'
  AND booked_for && '[2027-05-21 08:00+00, 2027-05-21 18:00+00)'::tstzrange;

-- EXCLUDE with condition: allow overlaps only for cancelled bookings
CREATE TABLE room_bookings (
  id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id   UUID NOT NULL,
  period    TSTZRANGE NOT NULL,
  status    TEXT NOT NULL DEFAULT 'confirmed'
    CHECK (status IN ('confirmed', 'cancelled')),

  -- Only exclude overlaps among non-cancelled bookings
  -- btree_gist needed for the status = column
  CONSTRAINT excl_room_no_double_book
    EXCLUDE USING GIST (
      room_id WITH =,
      period  WITH &&
    ) WHERE (status = 'confirmed')   -- ← Partial EXCLUDE: only applies to confirmed
);

Deferrable Constraints: Circular References

-- Problem: workspace references default_project; project references workspace
-- Normal FK creates a circular reference that blocks INSERT

CREATE TABLE workspaces (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            TEXT NOT NULL,
  -- FK to projects — can't reference before projects table exists
  default_project_id UUID
    REFERENCES projects(id)
    DEFERRABLE INITIALLY DEFERRED   -- ← Check at COMMIT, not statement level
);

CREATE TABLE projects (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  name         TEXT NOT NULL
);

-- Now you can insert both in one transaction:
BEGIN;

INSERT INTO workspaces (id, name) VALUES ('ws-uuid', 'Acme Corp');
-- default_project_id is NULL here — FK deferred until COMMIT

INSERT INTO projects (id, workspace_id, name)
VALUES ('proj-uuid', 'ws-uuid', 'Default Project');

UPDATE workspaces
SET default_project_id = 'proj-uuid'
WHERE id = 'ws-uuid';

COMMIT;  -- FK checked here — both records exist, constraint passes

Domain Types: Reusable Validation

-- Define once, use everywhere
CREATE DOMAIN email_address AS TEXT
  CHECK (VALUE ~ '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$');

CREATE DOMAIN positive_integer AS INTEGER
  CHECK (VALUE > 0);

CREATE DOMAIN percentage AS NUMERIC(5, 2)
  CHECK (VALUE >= 0 AND VALUE <= 100);

CREATE DOMAIN currency_code AS TEXT
  CHECK (VALUE ~ '^[A-Z]{3}$');   -- ISO 4217: USD, EUR, GBP

-- Use in tables
CREATE TABLE users (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email email_address NOT NULL UNIQUE,   -- Domain type
  name  TEXT NOT NULL
);

CREATE TABLE pricing_tiers (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  price_cents positive_integer NOT NULL,    -- Must be > 0
  discount    percentage NOT NULL DEFAULT 0, -- 0-100
  currency    currency_code NOT NULL DEFAULT 'USD'
);

-- Domain violation gives a clear error:
-- INSERT INTO users (email) VALUES ('not-an-email')
-- ERROR: value for domain email_address violates check constraint "email_address_check"

-- Alter domain validation (applies to all columns using it):
ALTER DOMAIN email_address
  ADD CONSTRAINT email_no_plus
    CHECK (VALUE NOT LIKE '%+%');  -- Disallow + aliases if needed

Constraint Naming Conventions

-- Naming convention: {table}_{columns}_{constraint_type}
-- Makes error messages self-documenting

CREATE TABLE order_items (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id    UUID NOT NULL,
  product_id  UUID NOT NULL,
  quantity    INTEGER NOT NULL,
  unit_price  INTEGER NOT NULL,

  -- Named constraints: error message includes the name
  CONSTRAINT order_items_quantity_positive   CHECK (quantity > 0),
  CONSTRAINT order_items_unit_price_positive CHECK (unit_price > 0),
  CONSTRAINT order_items_order_id_fk         FOREIGN KEY (order_id)   REFERENCES orders(id),
  CONSTRAINT order_items_product_id_fk       FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT order_items_order_product_unique UNIQUE (order_id, product_id)
);

-- List all constraints on a table
SELECT
  conname        AS constraint_name,
  contype        AS type,  -- c=check, f=foreign key, p=primary key, u=unique, x=exclude
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass
ORDER BY contype, conname;

Handling Constraint Violations in TypeScript

// lib/db/constraint-errors.ts
import { Prisma } from "@prisma/client";

export interface ConstraintViolation {
  constraint: string;
  table:      string;
  detail:     string;
}

export function parseConstraintError(
  error: unknown
): ConstraintViolation | null {
  if (!(error instanceof Prisma.PrismaClientKnownRequestError)) return null;

  // P2002: Unique constraint violation
  if (error.code === "P2002") {
    const fields = (error.meta?.target as string[]) ?? [];
    return {
      constraint: `unique_${fields.join("_")}`,
      table:      (error.meta?.modelName as string) ?? "unknown",
      detail:     `Unique constraint failed on fields: ${fields.join(", ")}`,
    };
  }

  // P2003: Foreign key constraint violation
  if (error.code === "P2003") {
    return {
      constraint: "foreign_key",
      table:      (error.meta?.modelName as string) ?? "unknown",
      detail:     `Foreign key constraint failed on field: ${error.meta?.field_name}`,
    };
  }

  return null;
}

// For raw SQL constraint violations (CHECK, EXCLUDE):
export function parseRawConstraintError(error: unknown): string | null {
  if (!(error instanceof Error)) return null;
  const match = error.message.match(/constraint "([^"]+)"/);
  return match ? match[1] : null;
}

// Usage in Server Action:
export async function createBooking(data: BookingInput) {
  try {
    return await prisma.$queryRaw`
      INSERT INTO bookings (resource_id, user_id, booked_for)
      VALUES (${data.resourceId}::uuid, ${data.userId}::uuid, ${data.range}::tstzrange)
      RETURNING id
    `;
  } catch (err) {
    const constraintName = parseRawConstraintError(err);
    if (constraintName === "excl_booking_no_overlap") {
      return { error: "This time slot is already booked. Please choose a different time." };
    }
    throw err;
  }
}

Cost and Timeline Estimates

See Also

• PostgreSQL Schema Design for SaaS
• PostgreSQL Triggers and Audit Logging
• PostgreSQL Row-Level Security
• Prisma Advanced Patterns
• PostgreSQL Generated Columns

---

Working With Viprasol

Database constraints are the last line of defense against invalid data — they enforce rules even when application code has bugs, migrations run directly, or scripts bypass your API. Our team audits schemas for missing constraints, adds named CHECKs for business rules, implements EXCLUDE for booking/reservation systems (with btree_gist for mixed-type columns), and uses deferrable FKs to resolve circular reference deadlocks in complex schemas.

What we deliver:

• Named CHECK constraints: price > 0, dates ordered, plan/MRR cross-column rule, total = subtotal + tax
• btree_gist extension + EXCLUDE USING GIST for booking overlap prevention
• Partial EXCLUDE with WHERE (status = 'confirmed') for soft-delete-compatible bookings
• DEFERRABLE INITIALLY DEFERRED FK for circular workspace↔project reference
• Domain types: email_address, positive_integer, percentage, currency_code (ISO 4217)
• parseRawConstraintError for user-friendly constraint violation messages

Talk to our team about your database schema design →

Or explore our cloud and data services.