Constraints Cross-table integrity FOREIGN KEY · ON DELETE · CASCADE · SET NULL

FOREIGN KEY Constraint

Link two tables so the database — not your application — guarantees that every child row points at a valid parent. Includes the five referential options (NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT) and a parent-child cascade SVG.

What a FOREIGN KEY does

A foreign key links one table to another by saying "values in this column must already exist in that column over there." The table holding the foreign key is the child (or referencing) table; the one being pointed at is the parent (or referenced) table.

This is how relational databases enforce referential integrity — the guarantee that an order's customer_id always points at a real customer, that an order item's product_id always points at a real product, and so on. Without foreign keys, your application has to enforce these relationships, and any bug or corner case can leave orphaned rows behind.

Parent → child relationship

Syntax

[CONSTRAINT name] FOREIGN KEY (column_name, ...)
    REFERENCES parent_table (column_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

Demo dataset — customers & orders

SQL — Create & insert

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(100) UNIQUE
);

INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob',   'bob@example.com'),
('Carol', 'carol@example.com');

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT NOT NULL,
    total        NUMERIC(10, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders (customer_id, total) VALUES
(1,  100.00),
(1,   45.50),
(2,  220.75);

What the FK enforces

SQL

-- This succeeds — customer 3 exists
INSERT INTO orders (customer_id, total) VALUES (3, 99.99);

-- This fails — customer 99 does not exist
INSERT INTO orders (customer_id, total) VALUES (99, 50.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
--        "orders_customer_fk"
-- DETAIL: Key (customer_id)=(99) is not present in table "customers".

-- This fails too — can't delete a customer that has orders
DELETE FROM customers WHERE customer_id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
--        "orders_customer_fk" on table "orders"

The FK guarantees no orphan can be created and no parent can vanish while children still reference it — unless you tell the database what to do in that case using ON DELETE / ON UPDATE options.

Referential options

What should happen when someone deletes or updates a parent row that still has children referencing it? PostgreSQL gives you five behaviours:

Option	What it does on parent delete/update
NO ACTION	Default. Reject the change if children still exist. Check is deferred to end-of-statement.
RESTRICT	Same as NO ACTION but checked immediately — no deferral.
CASCADE	Apply the same change to children — delete them, or update their FK to the new value.
SET NULL	Set the child's FK column to NULL.
SET DEFAULT	Set the child's FK column to its declared DEFAULT value (which itself must reference a valid parent).

Example — ON DELETE CASCADE

"When a customer is deleted, also delete all their orders." Useful for tightly-owned children where the parent's existence is the only reason for them to exist.

SQL

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT NOT NULL,
    total        NUMERIC(10, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

⚠ Cascading delete is irreversible. A single DELETE customers WHERE id = 5 can wipe out hundreds of rows in orders, order_items, shipments, and so on along the chain. Use it for genuinely owned data only.

Example — ON DELETE SET NULL

"When a customer is deleted, keep the orders but clear their customer_id." Useful for analytical history that should survive the parent's removal.

SQL

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT,            -- must be nullable for SET NULL
    total        NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

The FK column has to be nullable; otherwise SET NULL would itself violate NOT NULL.

ON DELETE CASCADE — what happens

Adding and dropping FKs

SQL

-- Add a foreign key to an existing table
ALTER TABLE orders
    ADD CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE;

-- Drop it
ALTER TABLE orders
    DROP CONSTRAINT orders_customer_fk;

Adding the FK validates every existing child row against the parent. If any row references a non-existent parent, the operation fails until you clean things up.

Performance tip — index the FK column

PostgreSQL automatically indexes the parent's referenced column (it's the primary key, after all). It does not index the child's foreign-key column. For most apps that's a missed opportunity — joins on FK columns and cascading deletes both speed up dramatically with an index there.

SQL

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Recap

FOREIGN KEY links a child column to a parent's PK/UNIQUE column.
Inserts that don't match a parent row are rejected; parent deletes are blocked unless an option says otherwise.
Five referential options: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT.
CASCADE deletes can chain a long way — use it deliberately.
Index the FK column on the child side for join and cascade performance.