Skip to content
uencoder

Foreign Key

A foreign key is a column referencing the primary key of another table, establishing referential integrity between tables and preventing orphaned references.

Purpose

To guarantee referential integrity, keep related data consistent, and prevent references to nonexistent rows.

Mechanism

FOREIGN KEY (col) REFERENCES other(id). Constraint: inserted/updated foreign key values must exist in the referenced table primary key (or be NULL). Optional ON DELETE CASCADE/SET NULL defines cascading behavior. Foreign keys need indexes to speed up constraint checks and JOINs.

Use cases

  • Cross-table referential integrity
  • Cascading delete/update
  • ORM relationship mapping
  • Data consistency constraints

Examples

  • Input 
    FOREIGN KEY (user_id) REFERENCES users(id)
    Output 
    Referential integrity constraint

    Foreign key

Common errors

Unindexed foreign keys lock tables during cascades
Create an index on the foreign key column

Related terms

Primary Key
A primary key is the column or column combination that uniquely identifies each row in a table — non-null and unique; the row identity.
GLOSSARY
JOIN
JOIN is the SQL operation combining rows of multiple tables by a join condition; types include inner, left/right/full outer, and cross joins.
GLOSSARY
Index
An index is an auxiliary data structure (e.g., B+ tree, hash) maintained to speed up queries, trading space for time at the cost of write overhead.
GLOSSARY
Normalization
Database normalization is the process of decomposing tables to eliminate redundancy and update anomalies, with normal forms from 1NF to BCNF/5NF growing stricter.
GLOSSARY

References