Schema Design and ConstraintsLesson 3.2

How to use foreign keys and referential integrity in PostgreSQL

FOREIGN KEY constraint, REFERENCES clause, ON DELETE CASCADE RESTRICT SET NULL, ON UPDATE, deferred constraints, foreign key indexes

Foreign Keys

A foreign key enforces that a value in one table must exist in another. It is the database's mechanism for referential integrity — no orphaned rows.

Defining a foreign key

CREATE TABLE orders (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL
    REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  total       NUMERIC(10,2)
);

Delete behaviors

ON DELETE RESTRICT   -- block delete if child rows exist (default)
ON DELETE CASCADE    -- delete child rows automatically
ON DELETE SET NULL   -- set FK column to NULL on parent delete
ON DELETE NO ACTION  -- same as RESTRICT, checked at end of tx

Use RESTRICT for most business data. Use CASCADE when child rows have no meaning without the parent (e.g. order_items without an order). Use SET NULL for optional relationships.

Always index the foreign key column

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

PostgreSQL does not automatically create an index on the referencing column. Without it, every delete on the parent table results in a sequential scan of the child table. This is a common performance trap.