SQL Mastery
Intermediate 15 min read SQL Mastery

Schema Design and Constraints

Let the database refuse what the pipeline shouldn't write

Primary and foreign keys, unique constraints, NOT NULL and CHECKs are the enforceable guardrails that stop bad rows from ever entering your tables.

schemaprimary keyforeign keyconstraintnormalization
Prerequisites: Joins In Depth
Before we start

What you will learn

  • Design primary/foreign keys, unique, NOT NULL and CHECK constraints
  • Normalize to 3NF in OLTP without over-normalizing analytics
  • Use DDL migrations with constraints as gates
Introduction

What is this?

The schema is the shape of a table: types, keys and constraints. Constraints are the database-level guarantees — a unique index stops duplicate natural keys, NOT NULL rejects missing mandatory fields, CHECK rejects invalid ranges — before any app or pipeline can write corruption.

Motivation

Why do you need it?

  • Enforced constraints turn data bugs into loud insert failures at the boundary, not silent drift downstream.
  • Primary/foreign keys make integrity explicit and give the planner the cardinality info it needs.
  • A well-designed schema is documentation you can't forget to read.
Think about it

Mental model

Imagine

A building's load-bearing walls

Think of

Schema constraints

You can't remove a load-bearing wall without the building failing — that's a constraint. Removing it 'temporarily' (a missing UNIQUE or FK) holds the structure up until, one day, it doesn't.

Mechanics

How it works

  • PRIMARY KEY implies uniqueness + NOT NULL and is the row identity.
  • FOREIGN KEY limits a column to existing parent keys — orphans are rejected at write.
  • UNIQUE enforces no duplicates on non-key columns (email, natural identifiers).
  • NOT NULL and CHECK (amount >= 0) restrict value domains.
  • Indexes materialize keys/uniques; each adds the write tax a primary key always deserves.

Constraint placement around a table.

INSERT / batch load
Constraints checked
Accepted
Rejected
Integrity intact
Hands-on

Examples

A guarded fact table

A guarded fact table
CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY,        -- row identity
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  amount       NUMERIC(12,2) CHECK (amount >= 0),
  status       TEXT NOT NULL CHECK (status IN ('pending','paid','refunded')),
  created_at   TIMESTAMPTZ NOT NULL
);
CREATE UNIQUE INDEX uq_orders_native ON orders (native_ref);

Every guarantee is enforced in-engine: identity, referential integrity, domain ranges and dedup.

Real world

In production

  • A dedup layer relies on a UNIQUE constraint on the natural key so a double-load fails instead of duplicating.
  • An orders model has FKs to customers/items so orphan rows can't exist to confuse joins.
  • A CHECK(amount >= 0) rejects negative amounts at the boundary, so finance totals are trustworthy.
Guidelines

Best practices

  • Enforce identity and referential integrity at the DB, not in application code.
  • Normalize source-of-record to 3NF; denormalize deliberately in analytics marts.
  • Version schema changes with migrations and review N+1-FK implications.
  • Index the columns your hottest WHERE uses; let the PK drive identity, not semantics.
Watch out

Common mistakes

  • Making the natural key the PK so a business-meaning change rewrites history.
  • Forgetting FK constraints 'for speed', then debugging orphan joins quarterly.
  • CHECK-ing a range that drift makes obsolete, and disabling it 'temporarily' forever.
  • Over-normalizing an analytics mart so every query needs six joins.
Root cause analysis

Common issues

Error
UNIQUE constraint violated on retry
Root cause

Non-idempotent load re-inserted an already-loaded natural key

Fix

Make loads upsert or dedupe first; the constraint is doing its job.

Error
FK constraint blocked a load
Root cause

Parent rows didn't exist yet in the referred table

Fix

Order the load parents-before-children or defer the constraint to commit.

When things break

Debugging tips

  • A failing insert with a constraint error names the exact violated rule — read it before investigating.
  • Orphan rows appearing = an FK was bypassed or a legacy load ran without it.
  • When a check 'blocks a legit value', re-baseline the domain after reviewing with owners.
Interviews

Interview checkpoint

Practice

Hands-on lab

Design a Constrained Orders Schema
30 min · intermediate
In progress
Objectives
  • Write DDL with PK, FK, UNIQUE, NOT NULL and CHECK constraints
  • Prove the database rejects invalid writes loudly
  • Add a natural-key unique guard

Design a Constrained Orders Schema

30 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which constraint prevents two rows with the same email?

2

A NOT NULL constraint correctly models 'revenue could be unknown'.

Go deeper

Official documentation

PostgreSQL constraints
docs
SQLite schema constraints
docs