Foreign Key
A column in one table that references the primary key of another table, enforcing referential integrity between related tables.
What is Foreign Key?
In short
A foreign key is a column (or set of columns) in one database table whose values must match a primary key value in another table, which links the two tables together and stops you from inserting rows that point to a parent record that does not exist.
What a foreign key actually is
A foreign key is a rule attached to a column. It says: every value in this column must already exist as a primary key (or unique key) in some other table. The table holding the foreign key is the child; the table it points to is the parent.
Take an online store. You have a customers table with a primary key id, and an orders table with a column customer_id. If you declare customer_id as a foreign key referencing customers.id, the database guarantees that every order belongs to a real customer. You cannot insert an order for customer 9999 if no customer 9999 exists.
This guarantee is called referential integrity. Without it, you get orphan rows: orders that reference customers who were never created or were deleted, invoices tied to missing accounts, comments attached to deleted posts. Foreign keys push that consistency check down into the database engine so every application talking to the database gets the same protection automatically.
How it works under the hood
When you define a foreign key, the database registers a constraint. On every INSERT or UPDATE to the child table, the engine checks that the referenced value exists in the parent. On every DELETE or UPDATE to the parent, it checks whether any child rows still point to the row you are touching.
To make these checks fast, the parent side relies on the index that already backs its primary key. On the child side you usually want your own index on the foreign key column, because the engine looks up child rows during parent deletes and joins. Postgres, for example, does not auto-create that child index, so forgetting it can make deletes on big parent tables painfully slow.
You also choose what happens when a parent row is removed. ON DELETE RESTRICT (often the default) blocks the delete if children exist. ON DELETE CASCADE deletes the children too. ON DELETE SET NULL clears the child reference. So deleting a customer can either be refused, wipe their orders, or detach the orders, depending on the rule you picked.
When to use it and the trade-offs
Use foreign keys whenever a row in one table is meaningfully owned by or attached to a row in another, and you want the database to never let those links break. They are the cheapest insurance against data corruption and they document your schema: anyone reading the table definition sees exactly how tables relate.
The cost is write throughput. Every insert and delete pays for the integrity check and the index lookups, so very high-volume write paths sometimes drop foreign keys and validate references in application code instead. CASCADE rules can also cause surprises, like one delete quietly removing thousands of rows across several tables.
Foreign keys also complicate sharding. Once a parent and child can live on different database servers, the engine can no longer enforce the constraint across the network, which is why many large systems and most NoSQL stores skip foreign keys and accept eventual cleanup. For a normal single-instance relational database, the safe default is to keep them on.
A concrete example
Picture a blog with a posts table and a comments table. comments.post_id is a foreign key referencing posts.id, declared with ON DELETE CASCADE.
A user writes a post (id 42) and ten comments arrive, each row storing post_id = 42. Because of the constraint, nobody can insert a comment with post_id = 999 when no post 999 exists, so you never end up with comments floating in space.
When the author deletes post 42, the cascade rule fires and the database removes all ten comments in the same transaction. You did not write any cleanup code; the foreign key handled it. If you had used ON DELETE RESTRICT instead, the database would have refused to delete the post until you removed the comments yourself, which is the safer choice when accidental deletes are expensive.
Where it is used in production
PostgreSQL
Enforces foreign keys with configurable ON DELETE and ON UPDATE actions, but leaves it to you to index the child column.
MySQL (InnoDB)
The InnoDB engine supports foreign keys and auto-creates an index on the referencing column; the older MyISAM engine ignored them entirely.
MongoDB
Does not enforce foreign keys; you either embed related data in one document or store a reference and check it in application code.
Amazon DynamoDB
Has no foreign key concept at all, since cross-item integrity does not survive its partition-based scaling, so apps validate relationships themselves.
Frequently asked questions
- What is the difference between a primary key and a foreign key?
- A primary key uniquely identifies each row within its own table and cannot be null or duplicated. A foreign key lives in a different table and points at a primary key, linking the two; its values can repeat and, depending on the column, can be null.
- Can a foreign key be null?
- Yes, unless the column is also marked NOT NULL. A null foreign key means no relationship for that row, for example an employee with no assigned manager yet. If the value is not null, it must match an existing parent key.
- Can a table have more than one foreign key?
- Yes. An orders table can reference customers, products, and shipping_addresses all at once, each through its own foreign key column. A foreign key can also span multiple columns when the parent key is composite.
- What does ON DELETE CASCADE do?
- It tells the database that when a parent row is deleted, all child rows pointing to it should be deleted automatically in the same transaction. It is convenient but dangerous, since one delete can wipe out large amounts of related data.
- Why do some large systems avoid foreign keys?
- Two reasons. They add a check on every write, which costs throughput at high volume, and they cannot be enforced across multiple shards or servers. So heavily sharded relational setups and NoSQL stores often drop them and validate references in application code.
Learn Foreign Key hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
See also
Related glossary terms you might want to look up next.
Primary Key
A column (or combination of columns) that uniquely identifies each row in a database table. Must be unique and not null.
Normalization
Organizing database tables to reduce redundancy by splitting data into related tables connected by foreign keys. Follows normal forms (1NF, 2NF, 3NF).
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).