Constraints and Keys

A constraint is a rule the database itself guarantees. Application code can check the same rules, but application code has bugs, runs in several versions at once, and is not the only thing that writes to the database. A constraint holds regardless.

NOT NULL

The simplest and most valuable. Lesson 14 catalogued what NULLs do to every query that follows; NOT NULL is where you stop them entering.

CREATE TABLE employees (
  emp_id     INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  email      TEXT
);

The dataset's employees table declares first_name NOT NULL, and inserting a row without one is refused — the error names the constraint and nothing is written. That block has no Run button because a failing statement is exactly what it demonstrates, and the harness that checks this course requires every runnable example to succeed.

Default to NOT NULL. Allow NULL only where "unknown" or "not applicable" is a real, meaningful state — manager_id on the person at the top of the tree, shipped_at on an order that has not shipped.

PRIMARY KEY

Uniquely identifies a row. Implies NOT NULL and UNIQUE, and is indexed automatically.

CREATE TABLE departments (
  dept_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL
);

Natural versus surrogate. A natural key is real data that happens to be unique — an email address, an ISBN. A surrogate is a meaningless generated number or UUID. Prefer surrogate keys: natural keys turn out to change (people change email addresses), turn out not to be unique (two people, one shared address), and propagate into every foreign key that references them. When a natural key changes, every child row must change with it.

Keep the natural key as a UNIQUE constraint instead. You get the uniqueness guarantee without the propagation.

Composite primary keys — several columns together — are right for join tables: (student_id, course_id) on an enrolment table both identifies the row and prevents duplicate enrolments.

UNIQUE

Uniqueness without being the identifier. A table may have many.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email       TEXT UNIQUE
);

Two subtleties. Most databases allow multiple NULLs in a unique column, because NULLs are not equal to each other — so a UNIQUE email column does not stop two rows both having no email. (SQL Server is the exception, allowing only one NULL.) And uniqueness is exact: the course dataset holds both Acme Ltd and ACME LTD, which a plain UNIQUE on name would happily accept. For case-insensitive uniqueness you need a unique index on LOWER(name), or a case-insensitive collation.

FOREIGN KEY

Guarantees that a value exists in another table — referential integrity.

CREATE TABLE employees (
  emp_id  INTEGER PRIMARY KEY,
  dept_id INTEGER REFERENCES departments(dept_id)
);

Without it you get orphans: the course dataset contains an order whose customer id matches no customer, which lessons 20 and 22 kept finding. That row exists because nothing prevented it, and every report since has had to cope.

Referential actions decide what happens when the parent is deleted or updated:

ON DELETE CASCADE is convenient and dangerous — deleting one customer can silently remove years of orders. Use it where the child genuinely cannot exist alone (order lines belong to their order); use RESTRICT where the deletion should be a deliberate act.

A caution specific to this playground: SQLite does not enforce foreign keys unless PRAGMA foreign_keys = ON is set, and the sandbox does not enable it. So an insert referencing a non-existent department succeeds here and would be rejected by PostgreSQL. Do not take the playground's behaviour as the rule.

And from lesson 33: outside MySQL, a foreign key is not indexed automatically. Declaring the constraint and adding the index are two separate jobs.

CHECK

An arbitrary condition every row must satisfy:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  amount   DECIMAL(10,2),
  status   TEXT CHECK (status IN ('pending','shipped','cancelled','refunded'))
);

CHECK constraints are underused. A status column with no check accumulates 'Pending', 'PENDING' and 'pendng' over a few years, and every query grows a clause to cope. Constrain the domain at the point of entry.

Note a CHECK passes when the condition is unknown, so CHECK (amount > 0) still admits a NULL amount. Pair it with NOT NULL when that matters — three-valued logic reaching into the schema.

MySQL only began enforcing CHECK in 8.0.16; before that it parsed and ignored them, which is a memorable way to discover your constraints were decorative.

DEFAULT

CREATE TABLE orders (
  status    TEXT NOT NULL DEFAULT 'pending',
  placed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Defaults let you add a NOT NULL column to an existing table without a value for every historical row, and they keep "the obvious value" out of every INSERT.

Why constraints belong in the database

• Several applications write to it. Your service, a migration script, an analyst's session, a colleague fixing something by hand at 2am.
• Code has bugs; constraints do not have race conditions. A "check then insert" in application code is two operations with a gap between them. A unique constraint has no gap.
• They document intent better than a comment, because they cannot drift from the truth.
• The planner uses them. A NOT NULL column lets the optimiser discard NULL-handling branches.

The counter-argument — that constraints make migrations harder — is real and worth much less than the guarantees.

Common mistakes

• Nullable everything — every downstream query pays for it forever.
• Natural keys as primary keys — they change, and the change propagates.
• Expecting UNIQUE to stop duplicate NULLs, or case variants — it does neither.
• ON DELETE CASCADE by default — one delete, silent data loss.
• Validating only in the application — one script bypasses it and the data is wrong permanently.

Interview question

Why enforce constraints in the database rather than in the application?

Because the database is the one thing every writer goes through, and constraints cannot race the way check-then-write application logic can. They also survive application bugs, ad-hoc scripts and manual fixes, and the planner can use them. Naming the check-then-insert race is what makes the answer concrete.

Check yourself

1. Why prefer a surrogate primary key over a natural one?
2. Why does a UNIQUE constraint not prevent two rows with a NULL email?
3. When is ON DELETE CASCADE appropriate, and when dangerous?