On this page

Keys and Constraints

12 min read TextCh. 4 — Design and Modeling

Keys and Constraints

Constraints are rules that PostgreSQL enforces on your data at the database level. They are the last line of defense for data integrity — even if your application has a bug that tries to insert invalid data, the database will reject it and return a clear error.

Understanding the full set of constraints available in PostgreSQL — primary keys, foreign keys, unique, check, not null, and their referential action options — is essential for building a database that protects itself.

Primary Keys

A primary key uniquely identifies each row in a table. It combines two constraints: UNIQUE (no duplicates) and NOT NULL (must have a value). Every table should have a primary key.

Single-Column Primary Keys

-- Auto-incrementing integer (simplest, most common)
CREATE TABLE articles (
    article_id  SERIAL PRIMARY KEY,
    title       VARCHAR(500) NOT NULL
);

-- GENERATED ALWAYS AS IDENTITY (SQL standard, preferred in PostgreSQL 18)
CREATE TABLE articles (
    article_id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       VARCHAR(500) NOT NULL
);

-- UUID primary key (recommended for public APIs and distributed systems)
CREATE TABLE users (
    user_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       VARCHAR(320) NOT NULL UNIQUE
);

Composite Primary Keys

-- Used for junction tables in many-to-many relationships
CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL REFERENCES users(user_id),
    role_id  INTEGER NOT NULL REFERENCES roles(role_id),
    granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, role_id)
);

The composite primary key enforces that each user-role combination is unique — a user cannot be assigned the same role twice.

Foreign Keys and Referential Actions

A foreign key ensures that a value in one table corresponds to an existing row in another table. The REFERENCES clause defines the foreign key:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    ordered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Referential Actions

When a referenced row in the parent table is deleted or updated, PostgreSQL enforces the referential action defined for the foreign key:

Action On DELETE On UPDATE
RESTRICT (default) Error if children exist Error if children exist
CASCADE Delete all child rows Update child FK values
SET NULL Set FK column to NULL Set FK column to NULL
SET DEFAULT Set FK column to its default Set FK column to its default
NO ACTION Like RESTRICT but deferred Like RESTRICT but deferred 
-- CASCADE: deleting a user deletes all their sessions
CREATE TABLE sessions (
    session_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     INTEGER NOT NULL REFERENCES users(user_id)
                    ON DELETE CASCADE,
    expires_at  TIMESTAMPTZ NOT NULL
);

-- SET NULL: deleting a category un-assigns products from it
CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES categories(category_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
);

-- RESTRICT: cannot delete a customer who has orders
CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
                    ON DELETE RESTRICT
);

Choose CASCADE for data that has no meaning without its parent (sessions, log entries, notifications). Choose RESTRICT for important business records you want to protect from accidental deletion.

UNIQUE Constraints

UNIQUE ensures that no two rows in the table have the same value in the constrained column(s):

-- Single-column unique
CREATE TABLE users (
    user_id   SERIAL PRIMARY KEY,
    email     VARCHAR(320) NOT NULL UNIQUE,
    username  VARCHAR(50)  NOT NULL UNIQUE
);

-- Multi-column unique: the combination must be unique
CREATE TABLE team_memberships (
    user_id  INTEGER NOT NULL REFERENCES users(user_id),
    team_id  INTEGER NOT NULL REFERENCES teams(team_id),
    CONSTRAINT uq_team_membership UNIQUE (user_id, team_id)
);

UNIQUE automatically creates a B-tree index on the constrained column(s), so unique constraints also improve query performance on those columns.

UNIQUE allows multiple NULL values by default (since NULL is not equal to NULL in SQL). If you need exactly one NULL, use a partial index.

NOT NULL Constraints

NOT NULL prevents a column from containing NULL values. It is one of the most important constraints for data quality:

CREATE TABLE employees (
    employee_id  SERIAL PRIMARY KEY,
    full_name    VARCHAR(200) NOT NULL,    -- required
    email        VARCHAR(320) NOT NULL,    -- required
    department   VARCHAR(100),             -- optional (nullable)
    hired_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Nullable columns communicate "this information may be unknown or not applicable." Non-nullable columns communicate "this information is always required." Be explicit and deliberate about which columns can be NULL.

CHECK Constraints

CHECK enforces an arbitrary boolean expression on column values:

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price       NUMERIC(10,2) NOT NULL CONSTRAINT chk_price_positive CHECK (price > 0),
    stock       INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    weight_kg   NUMERIC(8,3) CHECK (weight_kg > 0),
    status      VARCHAR(20) NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'inactive', 'discontinued'))
);

Multi-Column CHECK Constraints

CHECK constraints can reference multiple columns in the same table:

CREATE TABLE promotions (
    promotion_id  SERIAL PRIMARY KEY,
    starts_at     TIMESTAMPTZ NOT NULL,
    ends_at       TIMESTAMPTZ NOT NULL,
    discount_pct  NUMERIC(5,2) NOT NULL,
    CONSTRAINT chk_date_range CHECK (ends_at > starts_at),
    CONSTRAINT chk_discount   CHECK (discount_pct > 0 AND discount_pct <= 100)
);

Adding Constraints to Existing Tables

-- Add a CHECK constraint
ALTER TABLE products ADD CONSTRAINT chk_weight_positive CHECK (weight_kg > 0);

-- Add a UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT uq_users_phone UNIQUE (phone_number);

-- Drop a named constraint
ALTER TABLE products DROP CONSTRAINT chk_weight_positive;

Named constraints can be referenced in error messages, logs, and ALTER TABLE operations.

UUIDs in PostgreSQL 18

PostgreSQL 13+ includes gen_random_uuid() built-in. PostgreSQL 18 also provides gen_random_uuid() without requiring the pgcrypto extension:

-- Built-in UUID generation (no extension needed in PostgreSQL 13+)
CREATE TABLE documents (
    doc_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       VARCHAR(500) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Generate a UUID for any purpose
SELECT gen_random_uuid();
-- → 550e8400-e29b-41d4-a716-446655440000

When using UUIDs as primary keys:

  • Pros: globally unique, safe to expose in URLs, easy to merge data from multiple sources
  • Cons: 16 bytes vs 4-8 bytes for integers, random UUID v4 causes B-tree index fragmentation at very high insert rates

For high-insert-rate tables, UUIDv7 (time-ordered UUID) is better than random UUIDv4 because it maintains index locality. PostgreSQL 18 introduces uuidv7() as a built-in function.

Inspecting Constraints

-- View all constraints on a table
SELECT
    conname                         AS constraint_name,
    contype                         AS type,
    pg_get_constraintdef(oid)       AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass
ORDER BY contype, conname;

-- Types: p=primary key, f=foreign key, u=unique, c=check, n=not null

Constraints are not just defensive programming — they are the specification of your data model encoded directly into the database. They make your schema self-documenting and guarantee that even the most unexpected code paths cannot corrupt your data. In the next lesson, you will learn how indexes make queries over millions of rows fast.

Use UUID v4 for Distributed IDs
SERIAL and BIGSERIAL are great for single-server databases, but UUID v4 (gen_random_uuid() from pgcrypto, or gen_random_uuid() built-in since PostgreSQL 13) is better for distributed systems, public-facing APIs (users cannot guess adjacent IDs), and merging data from multiple sources. The storage cost is 16 bytes vs 4-8 bytes — usually negligible.
ON DELETE CASCADE vs RESTRICT
ON DELETE CASCADE automatically deletes child rows when the parent is deleted — useful for session data or log entries tied to a user. ON DELETE RESTRICT (the default) prevents deletion of the parent if children exist, protecting you from accidental data loss. Choose based on your data lifecycle requirements.
Name Your Constraints
Always give constraints explicit names (CONSTRAINT my_name). PostgreSQL auto-generates names like customers_email_key or orders_customer_id_fkey, which are hard to read in error messages and logs. Named constraints produce error messages like 'violates constraint uq_users_email', which is immediately actionable.
sql
-- Full constraint showcase
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- for gen_random_uuid()

CREATE TABLE users (
    user_id      UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    email        VARCHAR(320) NOT NULL,
    username     VARCHAR(50)  NOT NULL,
    age          INTEGER      CHECK (age >= 13 AND age <= 150),
    role         VARCHAR(20)  NOT NULL DEFAULT 'student'
                     CHECK (role IN ('student', 'instructor', 'admin')),
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_users_email    UNIQUE (email),
    CONSTRAINT uq_users_username UNIQUE (username)
);

CREATE TABLE enrollments (
    user_id      UUID        NOT NULL REFERENCES users(user_id)
                     ON DELETE CASCADE,
    course_id    INTEGER     NOT NULL REFERENCES courses(course_id)
                     ON DELETE RESTRICT,
    enrolled_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    progress     NUMERIC(5,2) NOT NULL DEFAULT 0
                     CHECK (progress >= 0 AND progress <= 100),
    PRIMARY KEY (user_id, course_id)
);

-- Add a constraint to an existing table
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);

-- Named unique constraint (easier to drop later)
ALTER TABLE products
ADD CONSTRAINT uq_product_sku UNIQUE (sku);

-- Foreign key with ON DELETE SET NULL
ALTER TABLE orders
ADD CONSTRAINT fk_orders_coupon
FOREIGN KEY (coupon_id)
REFERENCES coupons(coupon_id)
ON DELETE SET NULL;

-- Deferrable constraint: checked at end of transaction
ALTER TABLE order_items
ADD CONSTRAINT chk_total_reasonable
CHECK (quantity * unit_price < 1000000)
DEFERRABLE INITIALLY DEFERRED;

-- View all constraints on a table
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'enrollments'::regclass;