Constraints in Action

Rules enforced on every write

A primary key uniquely identifies rows; UNIQUE prevents duplicates (such as two accounts with one email); FOREIGN KEY ensures references point to existing rows (and ON DELETE CASCADE or RESTRICT decides what happens when the parent is deleted); CHECK enforces conditions such as positive prices or allowed statuses; NOT NULL requires a value. Constraint violations raise clear errors, so bad data never lands silently, regardless of which application or script wrote it.

Let the database guard your data

Keys and constraints reject bad data; identity columns and RETURNING simplify inserts; NULLs need care.

Four rejected inserts, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. A zero price violates the CHECK, a duplicate email violates UNIQUE, customer 999 does not exist for the foreign key, and "lost" is not an allowed status. Each error names the constraint and the failing values.

INSERT INTO products (name, price) VALUES ('Free pen', 0);
INSERT INTO customers (name, email) VALUES ('Asha 2', 'asha@example.com');
INSERT INTO orders (customer_id, ordered_on) VALUES (999, '2026-10-01');
INSERT INTO orders (customer_id, status, ordered_on) VALUES (1, 'lost', '2026-10-01');

Output:

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (4, Free pen, 0.00, {}).
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(asha@example.com) already exists.
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".
ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
DETAIL:  Failing row contains (7, 1, lost, 2026-10-01).

Name constraints deliberately

Explicit names (CONSTRAINT price_positive CHECK ...) make errors and migrations easier to read than generated ones.

Quick check: Which constraint stops an order from referencing a customer that does not exist?

• UNIQUE
• FOREIGN KEY
• CHECK
• DEFAULT