What is it? #
Designing a table means deciding two things: what columns exist, and what rules those columns must obey.
The second part is the one people skip, and it is the one that matters over time. A constraint is a rule PostgreSQL enforces on every single write, forever, no matter which application, script or person is doing the writing.
That last point is the whole argument for constraints. Validation in your application code protects the path through your application code. A constraint protects the data itself — including from the migration script someone runs at midnight, the admin fixing something by hand, and the second service that gets written next year.
The constraints worth knowing are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT.
Think of it like this #
A constraint is a form that refuses to be submitted until it is filled in correctly.
You can put the same checks in the app — a polite message saying "email is required". But if someone bypasses the form and posts the data directly, only the rules built into the filing system itself still apply. Constraints are the rules built into the filing system.
Simple example #
A customers table needs an automatic id, a name that must be present, an email that must be present and must be unique, a signup date that fills itself in, and an age that cannot be negative.
That is one identity column, two NOT NULLs, one UNIQUE, one DEFAULT and one CHECK — every core constraint in a single small table.
Code #
-- ---------- CREATE TABLE with the constraints that matter ----------
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
age integer CHECK (age >= 0 AND age < 150),
country char(2) NOT NULL DEFAULT 'IN',
signed_up timestamptz NOT NULL DEFAULT now()
);
-- GENERATED ALWAYS AS IDENTITY -> PostgreSQL fills in 1, 2, 3, ... automatically
-- PRIMARY KEY -> unique AND never NULL; identifies exactly one row
-- NOT NULL -> the column must always have a value
-- UNIQUE -> no two rows may share this value
-- CHECK -> the condition must be true for every row
-- DEFAULT -> value used when the INSERT does not supply one-- ---------- What the constraints actually stop ----------
INSERT INTO customers (name, email) VALUES ('Asha', '[email protected]'); -- fine
INSERT INTO customers (name, email) VALUES (NULL, '[email protected]');
-- ERROR: null value in column "name" violates not-null constraint
INSERT INTO customers (name, email) VALUES ('Someone', '[email protected]');
-- ERROR: duplicate key value violates unique constraint "customers_email_key"
INSERT INTO customers (name, email, age) VALUES ('Kid', '[email protected]', -5);
-- ERROR: new row violates check constraint "customers_age_check"
-- Each of these is a bug that never reaches your data.-- ---------- Foreign keys: linking tables safely ----------
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL
REFERENCES customers(id)
ON DELETE RESTRICT, -- refuse to delete a customer who has orders
total numeric(10,2) NOT NULL CHECK (total >= 0),
placed_at timestamptz NOT NULL DEFAULT now()
);
-- The ON DELETE options, and what each means:
-- ON DELETE RESTRICT -> refuse the delete while children exist (safest default)
-- ON DELETE NO ACTION -> same, but the check can be deferred to end of transaction
-- ON DELETE CASCADE -> DELETE THE CHILD ROWS TOO <-- powerful and dangerous
-- ON DELETE SET NULL -> keep the child row, blank out the reference-- ---------- ALTER TABLE: changing an existing table ----------
ALTER TABLE customers ADD COLUMN phone text; -- add a column
ALTER TABLE customers DROP COLUMN phone; -- remove it (DATA IS LOST)
ALTER TABLE customers RENAME COLUMN name TO full_name; -- rename
ALTER TABLE customers ALTER COLUMN country SET DEFAULT 'US';
-- Add a constraint to a table that already has data:
ALTER TABLE customers ADD CONSTRAINT customers_age_sane
CHECK (age >= 0 AND age < 150);
-- This SCANS the whole table to verify existing rows, and takes a lock while it does.
-- Safer on a large, busy table: add it as NOT VALID, then validate separately.
ALTER TABLE customers ADD CONSTRAINT customers_age_sane
CHECK (age >= 0 AND age < 150) NOT VALID; -- applies to NEW rows only, fast
ALTER TABLE customers VALIDATE CONSTRAINT customers_age_sane; -- checks old rows, weaker lock-- ---------- Identity columns (the modern replacement for serial) ----------
id bigint GENERATED ALWAYS AS IDENTITY -- PostgreSQL always supplies the value
id bigint GENERATED BY DEFAULT AS IDENTITY -- you MAY supply one; otherwise it generates
-- Older tutorials use: id serial / bigserial
-- serial still works, but identity columns are the SQL-standard form and handle
-- permissions and ownership more cleanly. Prefer identity in new tables.-- ---------- Generated columns: computed and stored automatically ----------
CREATE TABLE order_lines (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
line_total numeric(12,2)
GENERATED ALWAYS AS (quantity * unit_price) STORED
);
INSERT INTO order_lines (quantity, unit_price) VALUES (3, 250.00);
SELECT * FROM order_lines; -- line_total is 750.00, computed for you
-- You cannot write to a generated column; PostgreSQL always derives it.
-- This guarantees the total can never disagree with the parts it is built from.-- ---------- DROP TABLE ----------
-- !! DESTRUCTIVE: this deletes the table AND every row in it. There is no undo.
-- !! Run it only on a test database, or with a verified backup in hand.
DROP TABLE order_lines;
DROP TABLE IF EXISTS order_lines; -- no error if it is already gone
-- DROP TABLE orders CASCADE;
-- CASCADE also drops anything depending on it (views, foreign keys from other tables).
-- Read what it is about to remove before running it.How it works #
A primary key is UNIQUE and NOT NULL combined, plus the declaration that this is the identifier for a row. PostgreSQL automatically creates an index behind it, which is why looking a row up by primary key is fast.
An identity column is backed by a sequence — a small counter object the server increments. GENERATED ALWAYS means PostgreSQL supplies the number and refuses attempts to set it manually, which prevents an application from accidentally colliding with the counter. One detail surprises people: sequence numbers are not reused when a transaction rolls back. Gaps in the ids are normal and harmless; the sequence guarantees uniqueness, not an unbroken run.
A foreign key makes PostgreSQL check, on every insert and update, that the referenced row exists. It also controls what happens when the parent is deleted. RESTRICT refuses the delete, which is the safe default. CASCADE deletes the children too — convenient for something like removing a user and their sessions, genuinely dangerous if you have not traced exactly how far the cascade reaches. A cascade can propagate through several tables, and there is no confirmation prompt.
CHECK constraints hold a condition that must be true for every row. They are cheap and catch a surprising amount: negative prices, quantities of zero, statuses outside a known list, end dates before start dates.
DEFAULT supplies a value when the insert does not. DEFAULT now() is evaluated at insert time, so each row records its own moment.
Adding a constraint to an existing table is the operation to be careful with. PostgreSQL must verify every existing row, which means scanning the whole table while holding a lock — on a large busy table, that is an outage. The NOT VALID two-step avoids it: the constraint immediately applies to new writes, then VALIDATE CONSTRAINT checks the old rows under a much weaker lock.
Generated columns are computed from other columns in the same row and stored. Because the database derives them, a line total can never drift out of step with the quantity and price it came from.
Real-world use #
Applications get rewritten; databases outlive them. Constraints are how correctness survives that. When a second service, an admin script or a data migration writes to the same tables, the constraints are the only rules that still apply.
The usual objection is performance. In practice, the cost of a foreign key check or a CHECK condition is very small compared with the cost of finding and repairing corrupt data months later — and repair is often impossible, because by then nobody knows what the correct value should have been.
Name your constraints deliberately when they matter. PostgreSQL generates names like customers_email_key, which is fine, but a named constraint produces an error message your application can recognise and turn into a helpful message rather than a generic failure.
Be deliberate about ON DELETE CASCADE. It is right for data that genuinely has no meaning without its parent — a user's sessions, an order's line items. It is wrong for anything you might need to audit or reconcile later. Many teams keep RESTRICT everywhere and delete deliberately, in the correct order, inside a transaction.
For a busy production table, treat every ALTER TABLE as an operation with a lock cost. Adding a nullable column with no default is fast in modern PostgreSQL. Adding a constraint, changing a type, or rewriting a table is not, and belongs in a planned migration.
Common mistakes #
- Relying on application-level validation alone, so any other writer can insert invalid data.
- Using ON DELETE CASCADE without tracing how many tables the cascade actually reaches.
- Adding a constraint to a large, busy table in one step instead of using NOT VALID then VALIDATE.
- Expecting identity column values to have no gaps — rolled-back transactions consume numbers.
- Running DROP TABLE or DROP COLUMN on production without a verified, recently tested backup.
Practice #
Design a subscriptions table with: an identity primary key, a foreign key to customers that refuses deletion while subscriptions exist, a plan name restricted to a known list, a monthly price that cannot be negative, a start date defaulting to now, and an end date that must be later than the start date. Then deliberately try to break each constraint with an INSERT and read the error message PostgreSQL gives you — knowing what each one looks like makes debugging much faster later.