Constraints, keys and normalisation

Primary, foreign and unique keys, CHECK and defaults, referential actions, the first three normal forms, and when duplicating data on purpose is the right call.

Keys and constraints

Constraints are the part of the schema that stays true whether or not your application code is correct. They are cheaper than validation in five services and they cannot be forgotten on a new code path.

CREATE TABLE customers (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT      NOT NULL UNIQUE,
  name       TEXT      NOT NULL,
  country    CHAR(2)   NOT NULL DEFAULT 'GB',
  age        SMALLINT  CHECK (age >= 0 AND age < 130),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  total       NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  status      TEXT NOT NULL DEFAULT 'open'
              CHECK (status IN ('open', 'paid', 'shipped', 'cancelled')),
  placed_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (customer_id, placed_at)
);

Referential actions

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

-- RESTRICT / NO ACTION : refuse to touch a parent row that still has children
-- CASCADE              : delete or update the children too
-- SET NULL             : null out the child column (it must be nullable)
-- SET DEFAULT          : fall back to the child column's default

-- add the index the foreign key needs, then validate on a big table
CREATE INDEX idx_orders_customer ON orders (customer_id);
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

• RESTRICT is the right default for money and audit data: it makes accidental mass deletion impossible rather than merely unlikely.
• CASCADE is convenient for owned children (an order line has no meaning without its order) and dangerous for anything shared, such as a lookup table.
• Deferred constraints let a transaction violate a rule internally and check it at COMMIT — the standard trick for reordering rows under a unique key.
• Adding a foreign key to a populated table validates every row; add it NOT VALID, clean the data, then validate separately to avoid a long exclusive lock.

Normal forms in practice

Normalisation is one idea repeated: store each fact once, in the place it belongs. The first three forms catch almost every real problem, and the rest of the theory rarely earns its keep in an application schema.

-- violating 3NF: country is a fact about the customer, repeated per order
CREATE TABLE orders_bad (
  order_id         BIGINT PRIMARY KEY,
  customer_id      BIGINT,
  customer_country CHAR(2),       -- duplicated, and free to drift
  total            NUMERIC(12,2)
);

-- 3NF: the fact lives once, in the table it belongs to
CREATE TABLE orders_good (
  order_id    BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (id),
  total       NUMERIC(12,2) NOT NULL
);

• A table with no key cannot be normalised — you cannot say what depends on what, and you cannot delete a row without losing a fact.
• Multi-valued attributes (tags, phone numbers, permissions) get their own table with a composite key, not a comma-separated column.
• Denormalise only after measuring a problem, and give the copy a single writer: a trigger, a job, or one code path. Two writers guarantee drift.
• Report on denormalised copies, transact on normalised tables. That split is the usual answer when both needs appear.

FAQ

Related

Creating and altering schema with DDL Data types, NULL and three-valued logic

Last refreshed 2026-09-18.