Primary Keys
A column (or set) that uniquely identifies a row. The handle the rest of the schema points at.
Level: beginner
What is it?
A primary key is the chosen candidate key used to identify each row in a table. It can be one column or a composite of multiple columns, and its values must be unique and non-null.
Why does it matter?
Stable row identity lets other tables refer to a record unambiguously and prevents duplicate entities from silently entering the data model. Choosing a key also affects index size and how relationships are represented.
Mental model
A primary key is a durable handle, not necessarily a descriptive value. A student number or generated UUID may remain stable while a person's name or email changes.
How it works
- A primary key must uniquely identify every row and cannot contain NULL.
- Composite keys are useful when identity naturally depends on multiple columns, such as (student_id, course_id) in enrollment.
- Surrogate keys simplify references but do not replace unique constraints on real-world business identifiers.
- Foreign keys reference a candidate/primary key in another table.
- Changing a primary key can cascade through many references, so stable key design matters.
Steps
- Create Students(student_id PRIMARY KEY, email UNIQUE, name).
- Use student_id in Enrollment rows as a foreign key.
- Attempt to insert the same student_id twice; the constraint rejects the duplicate.
- Allow a name or email to change without changing the row's identity.
Real-world usage
- Designing relational schemas and joins.
- Choosing natural versus surrogate identifiers.
- Diagnosing unique-constraint violations.
Common mistakes
- Using a mutable name as the sole primary key.
- Believing an auto-increment ID automatically prevents duplicate emails or business entities.
- Allowing nullable columns inside the primary key.
Interview questions
Can a table have more than one primary key?
A table has one primary-key constraint, but that key may contain multiple columns. Other candidate keys can be enforced with UNIQUE constraints.
When is a composite primary key useful?
When the combination itself defines identity, such as one enrollment per student-course pair. An explicit UNIQUE constraint may enforce the same business rule with a surrogate key.