Constraints and Defaults
NOT NULL, UNIQUE, CHECK, DEFAULT, and generated columns
Skills taught: pg.not-null, pg.unique, pg.check, pg.defaults, pg.generated-columns
NOT NULL, UNIQUE, and CHECK
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE, -- no nulls, no duplicates
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0,
discount NUMERIC(5,2) DEFAULT 0
CHECK (discount >= 0 AND discount <= 100), -- inline CHECK
category TEXT NOT NULL
CHECK (category IN ('electronics', 'clothing', 'food', 'other')),
weight_kg NUMERIC(8,2)
CHECK (weight_kg > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Named constraint (appears in error messages)
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);
-- Multi-column UNIQUE constraint
ALTER TABLE team_memberships
ADD CONSTRAINT uq_team_user UNIQUE (team_id, user_id);
Generated columns (PostgreSQL 12+)
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
quantity INTEGER NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
-- Automatically computed, stored on disk
total_price NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- You cannot INSERT into a generated column
INSERT INTO orders (quantity, unit_price) VALUES (3, 29.99);
-- total_price is automatically 89.97
SELECT total_price FROM orders WHERE id = 1; -- 89.97
Tip: Give constraints meaningful names. An unnamed CHECK constraint shows as 'orders_price_check' in errors — a named one shows as 'chk_positive_price', making error messages self-documenting.
Frequently Asked Questions
Can CHECK constraints reference other tables?
No — CHECK constraints can only reference columns within the same row. For cross-table validation, use triggers or application code. Example: CHECK that an order's total equals the sum of its line items requires a trigger, not a CHECK constraint.