FOREIGN KEY

A FOREIGN KEY constraint links a column (or set of columns) in one table to the primary key, or another unique constraint, of a second table. Its job is to enforce referential integrity — guaranteeing that a value stored in the foreign key column always corresponds to a real, existing row in the referenced table.

Worked example: orders and customers

SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

With this foreign key in place, the database will refuse to insert an order whose customer_id does not already exist in the customers table:

SQL

-- Fails: no customer with id 999
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (501, 999, '2024-05-01');

ERROR: insert or update on table "orders" violates foreign key
constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(999) is not present in table "customers".

Without this constraint, nothing would stop an application bug from inserting an order that points at a customer who does not exist — an orphaned row that silently corrupts any report or join relying on that relationship.

Warning

A foreign key restricts more than just inserts into the child table (orders) — by default, it also restricts operations on the parent table (customers). Deleting a customer row that still has orders referencing it, or updating that customer's primary key value, will be rejected unless you tell the database what should happen to the dependent rows.

ON DELETE and ON UPDATE actions

The ON DELETE and ON UPDATE clauses tell the database how to handle changes to the parent row instead of simply rejecting them:

Action	Behavior on delete/update of the parent row
RESTRICT (or NO ACTION, the default in most systems)	Reject the delete/update if any dependent rows still reference the parent row.
CASCADE	Automatically delete (or update) the dependent rows along with the parent row.
SET NULL	Set the foreign key column in dependent rows to NULL when the parent row is deleted/updated (requires the column to allow NULL).
SET DEFAULT	Set the foreign key column in dependent rows to its DEFAULT value.

SQL

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

With ON DELETE CASCADE, deleting a customer automatically deletes every one of their orders too — convenient, but destructive, since a single DELETE on customers can silently remove a large amount of related data.

Warning

Choose ON DELETE CASCADE carefully. It is appropriate for genuinely dependent data (e.g. deleting an order should cascade to delete its order_items), but dangerous for data you may want to preserve even after the parent is gone (e.g. deleting a customer probably shouldn't silently erase their entire order history — SET NULL or RESTRICT is often safer there).

Tip

When in doubt, start with ON DELETE RESTRICT (or leave it as the default). It forces deletions of referenced rows to be explicit and deliberate, rather than allowing a single careless DELETE to fan out and remove data across many tables.

Note

Foreign key columns are not indexed automatically by every database (behavior varies by engine). Since foreign keys are so frequently joined and filtered on, it is common practice to add an explicit index on foreign key columns for performance.