Foreign Key
A column or group of columns in one table that provides a link between data in two tables.
Detailed Explanation
A foreign key 'points' to the primary key of another table. This creates a relationship (e.g., an `Orders` table has a `user_id` foreign key pointing to the `Users` table). Foreign keys enforce 'Referential Integrity,' ensuring that you cannot have an order for a user that doesn't exist.
Quick Summary
A foreign key is the database telling you "this column must point to a real row in another table." It's how relational integrity becomes a constraint enforced by the engine, not just a hope held by the app.
Key Takeaways
- Defined with `REFERENCES other_table(id)`. The DB rejects writes that would create orphans.
- Configurable behavior on delete/update: CASCADE, RESTRICT, SET NULL, SET DEFAULT.
- Foreign keys should almost always be indexed, joins and cascading deletes are slow without it.
- Some teams skip FKs at huge scale for write throughput; this is an explicit tradeoff for stronger ops discipline.
- Foreign keys catch bugs in application logic that would otherwise corrupt data silently.
When to use it
- Modeling parent-child relationships (orders → users, comments → posts).
- Enforcing tenant isolation: a record can only reference rows owned by the same tenant.
- Cascading deletes for owned data (delete a user, cascade-delete their drafts).
- Schema migrations that benefit from the DB rejecting impossible states before they accumulate.
Common Mistakes
- Skipping foreign key constraints "for performance" without measuring, orphan data quietly accumulates.
- Forgetting to index the foreign key column, slow joins and surprising lock issues.
- CASCADE delete on data you actually want to keep, entire tables vanish from a single parent delete.
- Cross-shard or cross-database FKs that the engine can't enforce, application-level checks are required and often missing.
Foreign Key, Frequently Asked
Should I always use ON DELETE CASCADE?
Use it deliberately, never as a default. CASCADE is right when the child row has no independent existence (a comment without its post). It's dangerous when children carry value (an order without a user, preserve as historical record).
Do FKs hurt performance?
Slightly, on writes, the engine has to check the constraint. The cost is usually negligible compared to the bugs they prevent. The big performance question is whether the FK column is indexed; if not, joins suffer far more than the constraint check.