Constraints: the rules that keep bad data out

Constraints are rules the database enforces on every write — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT — so that rows which break them are refused instead of stored.

Why it matters

Application code has bugs, and data arrives from many places. Tom could sign up twice because nothing said emails must be unique. A rule in the database is checked on every insert and update, from every program, every time.

The idea

NOT NULL: a value is required.
UNIQUE: no two rows may share the value.
PRIMARY KEY: unique and not NULL — the row's identity.
FOREIGN KEY (REFERENCES): the value must exist in another table's key, and you choose what happens when that row is deleted (refuse, ON DELETE CASCADE, or ON DELETE SET NULL).
CHECK: any condition, such as price > 0 or plan IN ('free', 'pro').

If a write breaks a rule, the whole statement fails with an error and nothing is changed.

The rules at a passport desk. No photo, no passport (NOT NULL). One passport number per person (UNIQUE). The country you list must be a real country (FOREIGN KEY). Your age can't be negative (CHECK). The officer refuses the form; they don't "fix it later".

An officer might let a small mistake through. A constraint never does — which is the point, but it also means a bulk load can fail on row 9,999,999. Clean or check the data before loading, or load into a staging table first.

Syntax

email   TEXT NOT NULL UNIQUE,
price   INTEGER CHECK (price > 0),
dept_id INTEGER REFERENCES departments(dept_id) ON DELETE SET NULL

How it works

  1. Every INSERT and UPDATE is checked against every constraint on the table.
  2. subscribers.email is the primary key, so a second row with the same email is a conflict.
  3. ON CONFLICT (email) DO NOTHING tells the database to skip that row quietly instead of failing.
  4. Inserting an order for customer 99, who doesn't exist, breaks the foreign key: refused.
  5. Inserting a department with an existing dept_id breaks the primary key: refused.

Key points

Common mistakes

Not true: A UNIQUE constraint stops a column from holding two NULLs.
In fact: In most databases NULL is not equal to NULL, so UNIQUE lets several NULLs in. Add NOT NULL as well when the column must always be filled.

In the interview

Constraints let the database enforce integrity on every write. NOT NULL is for required values, UNIQUE and PRIMARY KEY for identity, FOREIGN KEY for valid references with a chosen delete behaviour, and CHECK for any rule on a row. I put these rules in the schema rather than only in application code, because the database sees every writer, and a violation fails the statement atomically instead of leaving bad data behind.

Part of Unit 6: Building tables and changing data in the free SQL course.