Foreign Keys and Relationships

The cross-reference

A foreign key is a cross-reference — a column whose values must match the primary key of another table. PostgreSQL enforces this at every INSERT and UPDATE, so you cannot accidentally have an order pointing to a customer that doesn't exist.

The four ON DELETE behaviours

What happens when you delete a row that other rows reference?

NO ACTION / RESTRICT (default): the delete is rejected. Use this when the existence of children must block parent deletion until they're cleaned up.
CASCADE: child rows are automatically deleted with the parent. Use for "owned" relationships — order_items belong to orders; if the order goes, so do the items.
SET NULL: child rows keep existing but lose the reference. Use for optional associations — articles can lose their author if the author is deleted.
SET DEFAULT: child rows get the column's default value (rare; usually only sensible if you've defined a "deleted user" sentinel row).

ON UPDATE

Symmetric to ON DELETE but rarely needed — primary keys should be immutable. If you find yourself reaching for ON UPDATE CASCADE, your primary key is probably wrong.

Code

Setting up a parent/child relationship·sql

CREATE TABLE departments (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE employees (
    id            INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          TEXT NOT NULL,
    department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL
);

-- This works:
INSERT INTO departments (name) VALUES ('Engineering');
INSERT INTO employees (name, department_id) VALUES ('Alice', 1);

-- This is rejected:
INSERT INTO employees (name, department_id) VALUES ('Bob', 99);
-- ERROR: insert or update on table "employees" violates foreign key constraint

CASCADE for owned children·sql

CREATE TABLE orders (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id  INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity  INTEGER NOT NULL CHECK (quantity > 0)
);
-- Deleting an order cascades to its items.
-- Deleting a customer with orders is rejected (RESTRICT).

Foreign Keys and Relationships

The cross-reference

The four ON DELETE behaviours

ON UPDATE

Code

External links

Exercise

Progress

Comments 0