Foreign Keys: The Glue of Relational Databases
A foreign key is a pointer from one table to another, linking related data like a user to their posts. It enforces referential integrity, ensuring a post can't exist without a valid user. The footgun is forgetting this blocks deletions of referenced records.
WHY IT EXISTS To model relationships between different types of data without costly duplication. Instead of storing a user's full name and email with every blog post they write, we can store all users in one table and simply refer to their unique ID in the posts table. This saves space and prevents update anomalies.
THE MENTAL MODEL A foreign key is like a phone number in your contacts list. A row in your call_log table doesn't store the person's name, address, and photo; it just stores their phone number. That number is a unique pointer to a single, complete entry in your contacts table. The database uses this pointer to guarantee that every call log entry is linked to a real contact.
HOW IT WORKS You declare a column in one table (the "child" table, e.g., posts.author_id) as a FOREIGN KEY that REFERENCES a primary key column in another table (the "parent" table, e.g., users.id). The database then enforces an "inclusion dependency constraint": any value inserted into posts.author_id must already exist in the users.id column. This creates a formal, enforced link between the two tables.
WHEN TO USE IT Use foreign keys whenever you have a one-to-many or many-to-many relationship between entities. This is the foundation of relational database design, used for linking things like orders to customers, comments to posts, or products to suppliers. It ensures your data remains consistent and valid.
WHEN NOT TO USE IT Some high-throughput systems or certain NoSQL architectures may omit foreign key constraints at the database level, choosing to enforce relationships in the application code instead. This trades the safety of database-level integrity for potential performance gains or easier horizontal scaling, but it shifts the burden of preventing orphaned data onto developers.
ONE CANONICAL EXAMPLE Imagine a posts table and a users table. The posts table has an author_id column, which is a foreign key referencing the id column in the users table. If you try to insert a new post with an author_id of 99, but no user with id 99 exists, the database will reject the insert. Similarly, if you try to delete a user who still has posts referencing them, the database will, by default, block the deletion to avoid creating orphaned posts.
Read the original → en.wikipedia.org
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.