In a relational database, what's the difference between a primary key and a foreign key, and what does referential integrity give you?

technical-conceptual · Junior level · software-engineering

What the interviewer is really asking

Assesses whether the candidate understands how relational tables are linked and how the database enforces consistency across those links, which underpins almost every schema they'll touch.

What to say

• Define each by its job: a primary key uniquely identifies every row in a table — unique, non-null, one per table. A foreign key is a column in one table that points at the primary key of another, which is how you model a relationship like 'this order belongs to that customer'.
• Explain referential integrity as the guarantee the foreign key buys you: the database refuses to store a foreign key value that doesn't exist in the referenced table, so you can't create an order pointing at a customer who isn't there, and on delete it forces you to choose a behavior (block, cascade, or set null) rather than silently leaving orphaned rows.
• Give a concrete shape: an orders table with a customer_id foreign key referencing customers(id) — the constraint means every order is always traceable to a real customer, and you find out at write time, not when a report later breaks.

What to avoid

• Conflate the two — saying a foreign key 'is also unique' or 'identifies the row'; a foreign key can repeat (many orders, one customer) and can often be null.
• Claim the application alone keeps the link valid — the point of the constraint is that the database enforces it even if a bug or a different service writes bad data.
• Forget the delete/update side — only mentioning inserts and ignoring what referential integrity does to deletes (orphan prevention, cascade choices).

Example answers

Strong: A primary key uniquely identifies each row — it's unique and non-null, like an id column. A foreign key is a column that references another table's primary key, which is how you link rows: an orders table with a customer_id foreign key pointing at customers(id). Referential integrity is the database enforcing that link — it won't let me insert an order with a customer_id that doesn't exist, and if I try to delete a customer who still has orders, it either blocks me or cascades the delete depending on how I set up the constraint. So I can't end up with orphaned orders pointing at a customer who's gone, and the error shows up at write time instead of corrupting a report later.

Weak: A primary key is the main key of the table and a foreign key is a key from a foreign table. They both make the row unique. Referential integrity just means the data is correct and the keys match up. I usually let the application handle making sure the references are valid.