What is Foreign Key?
Definition
A foreign key is a constraint requiring that a column's value in one table actually exists as a primary key or unique value in another table. It prevents orphaned records, such as an order pointing to a customer that does not exist, a guarantee known as referential integrity. ON DELETE and ON UPDATE rules also define what happens to dependent rows when the referenced row is deleted or changed.
Also known as: FK, foreign key constraint, referential constraint, REFERENCES constraint
Referential integrity: no orphans
The customer_id column in an orders table points at a row in customers. A foreign key guarantees, inside the database, that this pointer always leads to a row that exists. Inserting an order for a customer ID that is not there fails, and deleting a customer who still has orders is either blocked or handled according to the rule you defined.
Doing the same check in application code looks equivalent but is not reliable. Two concurrent requests can race each other, another service or a one-off script can write to the database directly, and someone eventually forgets a check. With the constraint in the database, none of those paths can produce inconsistent data.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL
REFERENCES customers (id) ON DELETE RESTRICT,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL
REFERENCES orders (id) ON DELETE CASCADE,
sku text NOT NULL,
qty integer NOT NULL CHECK (qty > 0)
);The referenced column is usually the target table's primary key, though any column guaranteed to be unique will do.
The ON DELETE options
| Rule | When the parent row is deleted |
|---|---|
NO ACTION (default) | Raises an error if dependent rows exist; in PostgreSQL the check can be deferred to the end of the transaction |
RESTRICT | Rejects the delete immediately if dependent rows exist; cannot be deferred |
CASCADE | Deletes the dependent rows too |
SET NULL | Sets the foreign key column in dependent rows to NULL |
SET DEFAULT | Resets the column to its default value; MySQL's InnoDB engine rejects this rule |
The same rules exist for ON UPDATE, but if primary keys are designed never to change you will rarely need them. Deferred checking helps when two rows that reference each other are inserted within the same database transaction.
Picking a rule per relationship
- Rows that only make sense as part of a whole: order lines mean nothing without their order, so
CASCADEis natural. - Independent records with history: a customer's orders and invoices may have to be retained for accounting. Choose
RESTRICTand deactivate the customer, or anonymise their personal data, instead of deleting the row. - Optional links: if the staff member assigned to a support ticket leaves, the ticket should survive, which makes
SET NULLthe right fit.
Watch cascade chains closely. In a chain such as customer → order → order line → return, a single DELETE can quietly remove far more data than anyone intended.
Performance and operations
- Indexing: deleting a parent row makes the database look for dependent rows in the child table. PostgreSQL does not automatically add an index on the referencing column, so you usually need to create one yourself; MySQL's InnoDB creates the required index on its own.
- Bulk loads: checking every row adds noticeable cost when loading millions of them.
- Adding a constraint to an existing table: all existing rows have to be validated. In PostgreSQL, adding the constraint as
NOT VALIDand runningVALIDATE CONSTRAINTafterwards shortens the time strong locks are held on large tables. - Service boundaries: foreign keys only work within one database. In a microservices architecture where each service owns its data, cross-service consistency has to be handled in application logic with events and compensating steps.