What is Foreign Key?
A column or set of columns in a database table whose values must match a primary key in another table, ensuring referential integrity between the two tables.
A foreign key is a column or combination of columns in a relational database table that establishes a link to the primary key of another table. It enforces referential integrity, meaning that any value inserted into the foreign key column must already exist in the referenced primary key column. This prevents orphaned records and maintains consistent relationships across tables.
When a foreign key constraint is defined, the database management system checks every INSERT or UPDATE operation on the child table against the parent table. If the new foreign key value does not match an existing primary key value, the operation is rejected. Foreign keys can also specify actions for cascading updates or deletes: ON DELETE CASCADE, for example, automatically removes child rows when the corresponding parent row is deleted. The SQL standard defines these behaviors in the CREATE TABLE or ALTER TABLE statements.
Foreign keys are a core component of normalized database design. They allow tables to represent entities and their relationships without duplicating data. In a typical e-commerce schema, an orders table would contain a customer_id foreign key referencing the customers table. This design ensures that every order belongs to a valid customer. Foreign keys work alongside primary keys, indexes, and constraints to form the relational model introduced by E.F. Codd in 1970.
Key facts
- A foreign key must reference a primary key or unique key in the parent table.
- Foreign key constraints are defined in SQL using REFERENCES in CREATE TABLE or ALTER TABLE.
- Cascading actions (CASCADE, SET NULL, RESTRICT) control behavior on parent row deletion.
- Foreign keys can be composite, referencing multiple columns in the parent table.
- Indexing foreign key columns improves join performance and constraint checking speed.
How it works in practice
Related terms
References
More in Databases
ACID
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties guaranteeing reliable database transaction processing, ensuring data integrity despite concurrent access or failures.
B-Tree Index
A B-Tree index is a self-balancing tree data structure that maintains sorted data for efficient insertion, deletion, and range queries in database systems.
BASE
BASE is a design philosophy for distributed databases that prioritizes availability and partition tolerance over immediate consistency, making it a looser alternative to ACID in NoSQL systems.
CAP Theorem
CAP theorem states that a distributed data system cannot simultaneously provide consistency, availability, and partition tolerance; it can only guarantee two of the three.
Connection Pool
A managed cache of database connections that applications reuse rather than opening and closing a connection for each query, reducing latency and server load.
Failover
Failover is the process of automatically or manually promoting a replica database to primary when the active node fails, ensuring continued availability.
Hash Index
A data structure that maps keys to storage locations using a hash function, providing constant-time equality lookups but no ordered or range scans.
Materialized View
A database object that stores the precomputed result of a query as a table, refreshed periodically or on demand to improve read performance and reduce computational overhead.
NoSQL
NoSQL is a family of non-relational database systems designed for flexible schemas, horizontal scaling, and high-throughput data access that traditional SQL databases cannot easily provide.
Read Replica
A read replica is an asynchronously updated copy of a primary database instance used to offload and scale read-only query traffic without affecting the source database's write performance.