Stop 06 · SQL DatabasesTopics · 10/14

Constraints & Keys

In short

Your app's validation will have a bug someday. Constraints are rules inside the database that refuse bad data, no matter which code tries to write it.

Rules enforced at the database level — primary keys, foreign keys, unique, not null, check constraints — that keep your data valid regardless of what application code writes it.

The blob, as an invigilator, checks admit cards at the exam hall door.

Think of it like this

The exam hall invigilator

No admit card, no entry. One seat, one roll number. The invigilator doesn't care how you reached the door; the rules apply to everyone. Constraints are that invigilator, checking every row at the database's door.

Watch it happen

Bad data at the door

API

Postgres

INSERT email = NULL

1/6A buggy signup form forgets to send the email.

The big ideas

What you need to know

PRIMARY KEY

Unique and never NULL. Every table should have one, usually an id.

FOREIGN KEY

Values must exist in the other table. ON DELETE decides what happens to child rows when the parent goes.

NOT NULL and UNIQUE

NOT NULL means the value is required. UNIQUE means no two rows can share it.

CHECK

Any rule you can write as an expression, like total > 0 or status IN ('paid', 'pending').

See it in code

Walk through the code

sql

1CREATE TABLE users (2  id     SERIAL PRIMARY KEY,3  email  TEXT NOT NULL UNIQUE,4  age    INT CHECK (age >= 13)5);6 7CREATE TABLE orders (8  id       SERIAL PRIMARY KEY,9  user_id  INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,10  total    NUMERIC(10, 2) NOT NULL CHECK (total > 0),11  status   TEXT NOT NULL DEFAULT 'pending'12           CHECK (status IN ('pending', 'paid', 'shipped'))13);

1/4Email is required and unique: no blank signups, no duplicates.

Keep validation in your API for friendly messages, and constraints in the database as the final safety net.

Save yourself the pain

Common mistakes

Oops

Checking 'is this email taken?' in code, then inserting.

Do this instead

Two requests can pass that check at once. Add a UNIQUE constraint and handle error 23505.

Oops

Putting ON DELETE CASCADE on every foreign key.

Do this instead

Cascade suits child rows like order items. For important records, keep the default so deletes are blocked while children exist.

Your mission

Try to break your tables

~15 min

Quick check

Two signup requests with the same email arrive in the same millisecond. What reliably stops a duplicate?

Go deeper

1Add a foreign key constraint and confirm the database rejects an invalid reference.
2Practice `UNIQUE` and `NOT NULL` constraints on a table you design.
3Learn `ON DELETE CASCADE` and other foreign key behaviors.