TopolearnWithout them, orphaned rows creep in and every join quietly loses or invents data.
A foreign key declares that a column's values must exist in another table's key. With
customer_id bigint NOT NULL REFERENCES customers (id)the database rejects an order for a customer that does not exist, and rejects deleting a customer who still has orders. That is referential integrity, enforced on every write, by every application, forever. Application-level checks cannot match it, because concurrent requests and ad-hoc scripts slip past them.
What happens when the referenced row is deleted or its key changes is configurable with ON DELETE / ON UPDATE:
NO ACTION/RESTRICT(the default behaviour): refuse the delete while references exist.CASCADE: delete the referencing rows too. Right for owned children, such asorder_itemsof an order; dangerous for anything with independent value.SET NULL/SET DEFAULT: keep the child row and clear the reference.
The referenced columns must have a primary key or unique constraint. The referencing column, however, is not indexed automatically in PostgreSQL (MySQL's InnoDB does create one). Without an index, deleting a customer forces a scan of orders to check for references, and joins from customers to their orders are slow. Index foreign-key columns as a rule.
Foreign keys are also documentation: anyone reading the schema, and many tools, can see how tables connect. When you load data in bulk with references in a tricky order, PostgreSQL lets a constraint be DEFERRABLE INITIALLY DEFERRED, checked at commit instead of per statement.
Example
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (id),
status text NOT NULL DEFAULT 'pending',
total numeric(10,2) NOT NULL,
ordered_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (customer_id); -- not created automatically
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
unit_price numeric(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO orders (customer_id, total) VALUES (999, 10);
-- ERROR: insert or update on table "orders" violates foreign key
-- constraint "orders_customer_id_fkey"
DELETE FROM orders WHERE id = 104; -- its order_items go too (CASCADE)
DELETE FROM customers WHERE id = 1; -- ERROR: still referenced from ordersRemember
- A foreign key guarantees every reference points at an existing row
- ON DELETE CASCADE for owned children, RESTRICT/NO ACTION otherwise
- PostgreSQL does not index the referencing column; add the index yourself
Watch out for
- Relying on application code instead of foreign keys, then finding orphaned rows months later.
- Putting ON DELETE CASCADE on references to valuable data, so one delete wipes out history.