Primary and foreign keys are fundamental database constraints that define uniqueness and relationships between tables. Understanding both is essential for designing normalized, reliable relational databases.
A primary key is a column (or combination of columns) that uniquely identifies every row in a table. No two rows can share the same primary key value, and the column cannot contain NULL. Common examples include auto-incrementing integers (id) or UUIDs. Every well-designed table should have exactly one primary key.
A foreign key is a column in one table that references the primary key of another table, creating a link between the two. It enforces referential integrity, meaning the database will reject any value in the foreign key column that does not exist in the referenced table. For example, an orders table might have a customer_id column that references the id column of a customers table.
Primary and foreign keys form parent-child relationships between tables. The table holding the primary key is the parent (referenced) table, while the table holding the foreign key is the child (referencing) table. This pairing allows you to JOIN tables and reconstruct related data without duplicating it across rows.
When a foreign key constraint is active, the database enforces referential integrity automatically. If you try to delete a parent row that is still referenced by a child row, the database will raise an error by default. You can configure cascade behavior — such as ON DELETE CASCADE — to automatically delete or nullify child rows when the parent is removed.
Both primary and foreign keys can span multiple columns, forming composite keys. A composite primary key uniquely identifies a row using two or more columns together, common in junction tables for many-to-many relationships. A composite foreign key then references all columns of that composite primary key, and every column in the set must match.
Always index foreign key columns — most databases do not do this automatically, and unindexed foreign keys cause slow JOINs and deletes. Avoid using mutable business data (like email or username) as a primary key; use a surrogate key such as an auto-increment integer or UUID instead. Also, be deliberate about cascade rules: silent cascading deletes can cause hard-to-trace data loss in production.
© RM Full Stack & AI Engineer · All guides · Roadmaps · Open the app