What is the difference between primary, foreign, and unique keys?

WHAT THIS TESTS: This question probes whether you understand relational integrity constraints as distinct concepts rather than interchangeable database keywords. Interviewers want to see that you can articulate why each constraint exists, what specific problem it solves, and how they interact inside a real schema. At the senior level, this is foundational; confusion here signals weak data modeling instincts and a shallow understanding of normalization.

A GOOD ANSWER COVERS: A good answer hits four things in order. First, define a primary key as the single chosen candidate key that uniquely identifies every row in a table and typically does not change over the life of the record. Second, define a foreign key as a column or set of columns that establishes a link between two tables by referencing the primary key of another table, thereby enforcing referential integrity and preventing orphaned records. Third, define a unique key as an alternate candidate key that also guarantees uniqueness but was not selected as the primary key; it prevents duplicate values across one or more columns without serving as the main join target. Fourth, describe a concrete scenario where a unique key is applied to a non-primary column, such as an email address or government ID, because the business requires uniqueness but the surrogate primary key remains an auto-incrementing integer for performance and stability.

COMMON WRONG ANSWERS: Common wrong answers include stating that unique keys are merely indexes created for query performance, conflating unique constraints with primary keys, or describing foreign keys as a type of unique key. Another red flag is failing to mention that unique keys can span multiple columns or omitting the concept of candidate keys entirely. Some candidates also incorrectly claim that a table must have a primary key before it can have a unique key, or that foreign keys automatically enforce uniqueness on the referencing side rather than just the referenced side.

LIKELY FOLLOW-UPS: Interviewers often follow up by asking how NULL values interact with unique constraints, whether a table can have multiple unique keys, or the performance implications of adding unique constraints. They may also ask you to compare a unique index versus a unique constraint, or to sketch a schema where a join table uses composite unique keys to prevent duplicate many-to-many relationships while still allowing fast lookups.

ONE CONCRETE EXAMPLE: Consider a users table in a SaaS application. The primary key is a bigint auto-incrementing user_id, chosen for stable joins and compact storage. However, the business requires that every account have a distinct email address. You place a unique constraint on the email column so the database rejects duplicate signups at the storage layer. The email is an alternate key, it uniquely identifies a user for business purposes, but it is not the primary key because email addresses can change and are less efficient as join targets than integer IDs. If a user updates their email, the primary key remains stable and all foreign key relationships stay intact.