Skip to content
teach

Lesson 6. Keys and Constraints

Mission link: Constraints are the only guarantees that survive every application, script and manual fix that ever touches the table. Anything checked only in application code is a convention, and conventions lose.
Primary source: PostgreSQL, 5.5 Constraints
Prerequisites: Lesson 3, Lesson 4

Warm-up

  1. ▢ A table is a bag rather than a set. What does that mean for two identical rows?
Check

Nothing prevents them, and no query can tell them apart. Only a constraint makes rows distinguishable.

  1. ▢ How many NULLs does a UNIQUE column accept?
Check

Any number. Two NULLs are not equal for the constraint's purposes, so the constraint does not apply to them.

Know this

A key is a set of columns whose values identify a row. A constraint is a rule the database checks on every write, whoever does the writing.

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    amount      numeric(12, 2) NOT NULL CHECK (amount >= 0),
    shipped_at  timestamptz,
    UNIQUE (customer_id, id)
);
Constraint Guarantees
NOT NULL the column always has a value
UNIQUE no two rows share a value, NULLs excepted
PRIMARY KEY UNIQUE and NOT NULL together, one per table
REFERENCES the value exists in another table's key
CHECK an expression is true for every row

Why a primary key is the pair and not just uniqueness

Lesson 3 established that UNIQUE permits several NULLs, so uniqueness alone does not identify a row: a table with a UNIQUE nullable column can hold a thousand rows nobody can address individually. A primary key adds NOT NULL, and the combination is what makes every row reachable.

Practical rules that follow: a primary key should be stable, so nothing that a human might edit; not meaningful, so it does not have to change when the meaning does; and narrow, because every foreign key and every index on the table carries a copy of it.

Natural against surrogate

A natural key is data that already identifies the row, such as an email address or an ISO country code. A surrogate key is a value invented for the purpose, usually a generated integer or a UUID.

Natural Surrogate
meaningful to a human yes no
stable when reality changes often not yes
joins carry the real value an opaque number
exposes information sometimes, in URLs and logs no

The decisive question is stability. An email address identifies a customer until they change it, and then every foreign key referencing it has to change too. That is why most schemas use a surrogate primary key and a UNIQUE constraint on the natural key, which gets both properties: a stable identity and an enforced business rule.

For the surrogate, prefer bigint with an identity or sequence default. A UUID is the right answer when identifiers must be generated by clients or merged across systems, at the cost of being wider and, for random UUIDs, less friendly to the index structures in stage 6.

Foreign keys, and what happens on delete

customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT

A foreign key says the referenced row exists. The target must be a primary key or a UNIQUE constraint, because otherwise "the referenced row" is ambiguous.

The ON DELETE action is a design decision, not a detail:

  • RESTRICT or NO ACTION: refuse to delete a customer with orders. The default, and usually correct, since it forces the question to be answered explicitly.
  • CASCADE: delete the orders too. Right for genuinely owned children, and dangerous everywhere else, because one statement can remove far more than the author expected.
  • SET NULL: keep the order and forget the customer. Requires the column to be nullable, which contradicts NOT NULL, so it is a decision about what the data means.

A foreign key is also the fact that makes joins trustworthy, and the fact a planner can use. Removing them "for performance" removes both.

CHECK constraints carry the rules that have nowhere else to live

CHECK (amount >= 0)
CHECK (shipped_at IS NULL OR shipped_at >= created_at)
CHECK (country IN ('GB', 'FR', 'DE'))

A CHECK is evaluated per row and must not be unknown to pass, so the NULL handling is deliberate: the second example above spells out the null case rather than relying on it.

A closed set of values is better as a CHECK or a lookup table than as a native enum type, because both can gain a value without the locking behaviour that altering a type involves.

The argument worth having once

Application-level validation and database constraints are not alternatives, because they do different jobs. Application checks give good error messages to a user, and they cover exactly the paths that go through the application. Constraints cover every path: a second service, a migration script, a data fix typed into a console at midnight, a restore from an old backup.

Data outlives the code that wrote it. That is the whole argument, and it is why "we validate in the application" is an answer to a different question.

Practice

  1. ▢ A table has email text UNIQUE with no NOT NULL. Give a state that satisfies the constraint and still breaks the application's assumption of one row per customer.
Hint

One row of the table in lesson 3 answers this. Ask what the constraint does when it has nothing to compare.

Check

Any number of rows with email IS NULL. The constraint permits them, because two NULLs are not equal for its purposes, so a table can hold a thousand customers with no email and no way to distinguish them.

Adding NOT NULL is what makes the constraint mean what the application assumes. That pair, UNIQUE plus NOT NULL, is a candidate key.

  1. ▢ Which of these should be the primary key of a customers table?

    • a) email
    • b) A generated bigint, with UNIQUE on email
    • c) (email, country)
    • d) A random UUID, with UNIQUE on email
Check

b) in most systems, and d) when identifiers must be generated outside the database.

Option a fails on stability: an email change would have to propagate to every referencing row. Option c is worse, since it is wider and no more stable. Option d is correct in distributed or client-generated cases and costs width and, for random values, index locality that stage 6 will care about.

What b and d share is the important part: a surrogate key for identity, plus a UNIQUE constraint that keeps the business rule enforced.

  1. ▢ ON DELETE CASCADE is on orders.customer_id. A cleanup script deletes 200 inactive customers. What happens, and what would you have preferred?
Check

Every order belonging to those customers is deleted too, in the same transaction, with no separate confirmation and no obvious record of how much was removed.

RESTRICT would have been preferable here: the delete fails, and whoever wrote the script has to decide what should happen to the orders. That is a question worth being forced to answer, since orders are usually financial records rather than owned children of a customer row.

CASCADE earns its place where the child genuinely cannot exist alone and has no independent value, for example an order's line items relative to the order.

  1. ▢ Write the constraint for each rule: an amount is never negative; a shipping date, when present, is not before the order date; a discount percentage is between 0 and 100.
Check 
CHECK (amount >= 0)
CHECK (shipped_at IS NULL OR shipped_at >= created_at)
CHECK (discount BETWEEN 0 AND 100)

The middle one is the instructive case: without the explicit IS NULL branch, the expression is unknown for an unshipped order. A CHECK passes on true and on unknown in the standard, which means the naive version happens to work, and stating the null case is still better because the intention is then readable and does not depend on that rule.

  1. ▢ A team removed all foreign keys to speed up bulk imports and kept the checks in the application. Name three things they lost.
Check

First, the guarantee. Every path that is not the application, meaning migrations, scripts, another service, a restore, can now create an order pointing at a customer that does not exist, and nothing reports it.

Second, information the planner uses. A foreign key tells the engine about the relationship between the tables, which can affect join strategies and, in some engines, allows join elimination.

Third, the ability to know whether it already happened. Once the constraint is gone, orphan rows accumulate silently, and adding the constraint back later requires finding and fixing every one of them first, which is a much larger job than the import it was removed for.

The legitimate version of what they wanted is to drop the constraint for the import inside a transaction, or to defer it, and then restore it, so the window is bounded and the data is validated at the end.

Real-world reps

  • [ ] Create the orders table with the CHECK and the foreign key, then try to insert a negative amount and an order for a nonexistent customer. Read both error messages.
  • [ ] Make a UNIQUE column nullable, insert three NULLs, and confirm the constraint allows it.
  • [ ] Tomorrow: pick a table you work with and list the rules its data obeys. Then check how many are enforced by the database rather than by the code that happens to write it.

Going further

Not landing? Reread the primary source at the top, since this lesson compresses it and compression is where understanding leaks. Check the glossary for any term that felt slippery.

If the lesson itself is unclear rather than the material, that is a defect: open an issue.

  • Created: August 31, 2026
  • Last updated: September 7, 2026
  • 7 min read
Table of contents