Tables, keys, constraints & schema design

Schema design decides what invalid states the database can represent. Strong constraints move critical correctness rules closer to the data and protect every application that writes to the database.

CREATE TABLE and data types

DDL examples in this chapter are intentionally shown as static SQL rather than runnable learning blocks because the site's general learning runner protects schema-changing statements.

CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Run the query to see the result.

Choose types that represent the domain accurately. Engine-specific type systems differ, especially around booleans, timestamps, JSON, arrays, UUIDs, identity columns, and numeric precision.

Primary keys

A primary key must uniquely identify a row and should be stable. Natural keys can work when the business identifier is truly immutable; surrogate numeric/UUID keys are often simpler for internal relationships.

Foreign keys and referential integrity

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id)
);

Run the query to see the result.

Foreign keys prevent orphan references when enforcement is enabled and configured correctly. ON DELETE/ON UPDATE actions such as RESTRICT, CASCADE, SET NULL, and SET DEFAULT should match the domain, not convenience.

NOT NULL, UNIQUE, CHECK, DEFAULT

Constraints are executable business rules.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  balance NUMERIC NOT NULL DEFAULT 0,
  status TEXT NOT NULL CHECK (status IN ('active', 'blocked', 'closed'))
);

Run the query to see the result.

A UNIQUE constraint involving nullable columns can behave differently across engines, so verify target-database semantics.

Composite keys and uniqueness

Some relationships are naturally unique only across multiple columns, such as (order_id, product_id) or (tenant_id, external_id). Composite UNIQUE constraints express that invariant directly.

Normalization

Normalization reduces duplicated facts and update anomalies. Practical OLTP design commonly aims for well-structured entities where each fact has one authoritative place. Denormalization can be justified for performance/read models, but it should be deliberate and accompanied by a synchronization strategy.

ALTER and migration safety

Schema changes happen over time. Safe migrations consider existing rows, lock duration, backfills, application compatibility, rollback strategy, and deployment order. “ALTER TABLE succeeded in development” is not enough evidence for a large production table.

Schema-design review questions

• What uniquely identifies a row?
• Which values are mandatory?
• Which values must be unique?
• Which relationships must always point to an existing row?
• Which state combinations are invalid?
• What happens when a referenced row is deleted?
• Which columns are searched/sorted often enough to consider indexing?

Practice: design a test_run table with a primary key, unique external run id, required status, start/end timestamps, and a CHECK restricting status values.