Explain Constraints & Referential Integrity in Databases

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.

Constraints let the database itself enforce the rules your data must obey, rather than trusting every application code path to remember them. The difference matters because data usually outlives the code that created it and is touched by many programs, scripts, and migrations over its lifetime; an invariant enforced in one service's validation layer is one a batch job or an ad-hoc fix can quietly violate. A constraint in the schema is a guarantee that holds no matter what writes the data.

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

The core constraints each express a kind of invariant. NOT NULL requires a value to be present. UNIQUE forbids duplicate values in a column or combination of columns — the basis of an alternate key like a unique email. CHECK enforces a domain rule on a row, such as price >= 0 or status IN ('active','closed'). PRIMARY KEY combines uniqueness and not-null to give each row a canonical identity. And the FOREIGN KEY enforces referential integrity: a value in one table must reference an existing row in another, so you cannot create an order for a customer that does not exist, and the database can be told what to do when the referenced row is deleted — RESTRICT to block it, CASCADE to delete the dependents, or SET NULL to orphan them. Together these make whole categories of corrupt data structurally impossible to insert.

NOT NULLUNIQUECHECKprice >= 0status IN ('active','closed')

The principle interviewers want articulated is defence in depth: validate in the application for fast, friendly feedback, but back it with database constraints as the last line of defence that cannot be bypassed. The trade-offs worth acknowledging are that foreign keys add a small write-time cost and require care around bulk loads and ordering, and that CASCADE deletes are powerful but can remove more than intended — so they reward deliberate configuration over blanket use.

CASCADE