Primary Key vs Candidate Key: Key Differences & When to Use Each
A Primary Key is the single column or set of columns chosen to uniquely identify every row in a table. A Candidate Key is any column or combination that could serve this role—there may be several, but only one becomes the Primary Key.
Developers mix them up because SQL engines quietly treat every Candidate Key as a “unique constraint” that looks almost identical to a Primary Key. In design meetings, the debate feels like picking a company name—everyone knows the brand must be unique, but which one feels right?
Key Differences
Primary Key: one per table, no NULLs, auto-indexed, becomes the default parent in foreign-key relationships. Candidate Key: many allowed per table, can hold NULLs if the DB permits, not automatically indexed, and only one of them gets promoted to Primary Key.
Which One Should You Choose?
Promote the Candidate Key that is shortest, never changes, and is meaningful to users (e.g., email over UUID). Keep the remaining Candidates as unique constraints to catch duplicate social-security numbers or ISBNs without the overhead of extra foreign-key cascades.
Examples and Daily Life
Imagine an e-commerce “Customer” table: email, phone, and username are all Candidate Keys. You pick email as the Primary Key because it’s globally unique and stable. Phone and username stay as additional unique constraints to prevent duplicates without breaking order-history links.
Can a table have no Candidate Keys?
Yes, but then it can’t have a Primary Key either; rows would be indistinguishable, violating relational design.
Is a Primary Key always numeric?
No. Strings like email or composite keys like (country_code, passport_no) work fine as long as they’re unique and non-NULL.