All topics
12 / 66
schema design

Constraints & Referential Integrity

Use NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints so the database enforces invariants — instead of relying on every code path to do so.

A constraint is a rule the database itself enforces. It cannot be bypassed by any code that writes to the table.

A passport office: every field on the form must be filled, every reference number must match a real entry. The clerk doesn't trust you to remember.

Key Concepts

1
Why that matters more than it first appears. Data outlives the code that created it. Over a table's life it will be written by the original service, a rewrite of that service, a batch import, a migration script, and somebody at 2 a.m. fixing an incident by hand.
2
A rule living in one service's validation layer holds for that service. A rule in the schema holds for all of them.
3
The five constraints.NOT NULL — a value must be present.
NOT NULL
4
UNIQUE — no duplicates in this column, or in this combination of columns. This is how you express an alternate key such as an email address.
UNIQUE
5
CHECK — a rule about one row: price >= 0, or status IN ('active','closed').
CHECKprice >= 0status IN ('active','closed')
6
PRIMARY KEY — unique and not null together, giving the row its identity.
PRIMARY KEY
7
FOREIGN KEY — this value must exist in another table. You cannot create an order for customer 99 if customer 99 does not exist.
FOREIGN KEY
8
What happens when the referenced row is deleted. A foreign key must say, and the choice has teeth:
9
    ON DELETE RESTRICT   refuse the delete while children exist  (the safe default)
    ON DELETE CASCADE    delete the children too
    ON DELETE SET NULL   keep the children, blank the reference
10
CASCADE is the one to be careful with. Deleting a customer can remove their orders, and the order lines under those orders, and the shipments under those — one statement, a chain you did not picture. Use it where the child genuinely has no meaning without the parent, and prefer RESTRICT elsewhere.
CASCADERESTRICT
11
Constraints and application validation are not competing. Validate in the application so the user gets a clear message on the right field. Keep the constraint so the rule is still true when a script writes directly. The application layer is for the message; the database layer is for the guarantee.
12
The costs, honestly. A foreign key costs a lookup on every insert and update, so bulk loads run faster with them dropped and re-added — but you must re-add them, and validate. Foreign keys also force insert ordering: parents before children. And an unindexed foreign key column makes deleting a parent scan the child table, which is a common and easily missed cause of slow deletes.
13
What the interviewer is probing.1. "Why enforce constraints in the database rather than the application?" Probing: the argument. Stalls: "The app is enough." Moves up: the database is the last line — several applications, scripts and migrations touch the data, and only the constraint applies to all of them.
14
2. "What does ON DELETE CASCADE risk?" Probing: the hazard. Stalls: "Nothing." Moves up: one delete silently removing a large subtree, which is why many teams prefer RESTRICT and an explicit deletion path.
15
3. "What is the performance cost of a foreign key?" Probing: the trade. Stalls: "None." Moves up: a check on every insert and update, and a lock on the referenced row — usually worth it, occasionally not at very high write rates.
16
4. "Why does a unique constraint need an index?" Probing: the mechanism. Stalls: "It does not." Moves up: uniqueness is enforced by a unique index, so the constraint creates one — which is also why it costs on writes.
Use cases:ConstraintsFKIntegrity