Foreign Key

A foreign key is the mechanism that links tables in a relational database. It is a column (or set of columns) in one table whose values must match the primary key of another table, establishing a relationship between the two and guaranteeing that references point to real rows.

How It Works

Consider an orders table with a customer_id column that references the id primary key of a customers table. The customer_id is a foreign key. The database enforces referential integrity: you cannot insert an order for a customer that does not exist, and the database controls what happens when a referenced row is deleted or updated through actions such as:

• RESTRICT / NO ACTION: block the change if dependent rows exist.
• CASCADE: propagate the delete or update to dependent rows.
• SET NULL / SET DEFAULT: clear or reset the foreign key in dependents.

Foreign keys are central to modeling one-to-many and many-to-many relationships (the latter via a join table with two foreign keys).

Why It Matters

Foreign keys prevent orphaned and inconsistent data at the database level, independent of application code. They make relationships explicit, self-documenting, and reliably enforced even when multiple applications write to the same database.

The trade-offs are some write overhead from constraint checks and added complexity in sharded or distributed systems, where cross-node foreign keys are often impractical. Indexing foreign key columns is recommended, since joins and cascade checks rely on them.

Related Terms

A foreign key references a primary key, is a core tool of normalization, and is part of the integrity guarantees alongside ACID.