# Constraints in Action — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/t-constraints

> PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL.

## 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.

![Three ideas: constraints, identity and RETURNING, NULL semantics.](assets/figures/postgresql/section-2-map.svg) — Figure 2.1 — Constraints, identity and NULLs.

## 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.

```sql
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.

**Quiz:** Which constraint stops an order from referencing a customer that does not exist?

- [ ] UNIQUE
- [x] FOREIGN KEY
- [ ] CHECK
- [ ] DEFAULT

*Answer:* FOREIGN KEY. Referential integrity.