Foreign keys

A foreign key is a column or a group of columns in a relational database table that provides a link between data in two tables. The foreign key in one table points to the primary key — or any column or group of columns covered by a UNIQUE constraint — in another table. A foreign key may also reference its own table, forming a self-referential relationship, eg. an employee row whose manager_id column points back to the primary key of the same employees table.

A foreign key is a constraint that enforces referential integrity, ie. it ensures that the relationship between two tables remains consistent, a core requirement of data integrity. The constraint is declared in the SQL schema as part of data modeling, and once in place the database itself rejects any insert, update, or delete that would leave a dangling reference.

Referential actions

When a row on the parent side of a foreign key is deleted or its key columns updated, the database must decide what to do with the child rows that reference it. SQL lets the schema declare a referential action for each case.

  • CASCADE. Propagate the delete or update to the child rows. Deleting a customer cascades to all of their orders. Convenient, but a single delete can touch many tables and is hard to undo outside a transaction.
  • SET NULL. Set the child’s foreign key columns to NULL. Only valid when the column is nullable, and only sensible when the child can meaningfully outlive the parent, eg. an order retained after its originating customer is removed.
  • SET DEFAULT. Replace the foreign key with its declared default. Rarely used, and only valid when that default still satisfies the constraint.
  • RESTRICT and NO ACTION. Both forbid the delete or update when child rows reference the parent. The difference is timing. RESTRICT is checked immediately, while NO ACTION is checked at the end of the statement or, if the constraint is deferrable, at transaction commit, allowing intermediate states that re-order operations within the same transaction.

The choice of action encodes a business rule about the lifetime relationship between parent and child. It is a data modeling decision, not a performance knob.

Indexing

Indexing the foreign key column speeds up joins and the cascade checks that enforce referential integrity. See database indexes and database query optimization.

Unlike primary key indexes, which a database creates automatically, most engines do not auto-create an index for a foreign key. An unindexed foreign key is a common cause of slow deletes and updates on the parent table, because the engine must scan the child table to verify that no reference will be broken, and of escalation to table-level locking during those checks. The practical rule is to index every foreign key column unless there is a measured reason not to.

Trade-offs

Foreign key constraints are not free. Every insert, update, and delete on either side of the relationship pays an extra lookup to verify the constraint, and bulk loads can be slowed substantially when many constraints must be checked row by row. Within a transaction, most engines let the check be deferred until commit (DEFERRABLE in standard SQL), so that mutually referencing rows can be inserted in any order and the constraints only have to hold at the end. This keeps the ACID guarantee intact while removing the need to sequence statements around the constraints.

Constraints enforced inside a single database do not extend across service or storage boundaries. NoSQL stores and most microservice setups have no foreign keys spanning their data, so referential integrity becomes the application’s responsibility and is often only eventual. The loss of database-enforced foreign keys is one of the chief trade-offs of moving data out of a single relational database.

See also