What Are Primary Keys?
You'll learn: Why every table in a well-designed database needs a unique identifier for each row, and how primary keys provide that guarantee.
The Core Idea
A primary key is a column (or combination of columns) in a database table that uniquely identifies each row. Think of it like a fingerprint or a social security number—no two people share the same one, and you can always use it to find exactly one specific person.
Primary keys serve two critical purposes:
- Uniqueness: Each value in the primary key column must be unique across the entire table. No duplicates allowed.
- Non-nullability: A primary key can never be
NULL. Every row must have a valid identifier.
Why Primary Keys Matter
Imagine a customers table without primary keys. If you have two customers named "John Smith" living at different addresses, how do you reliably update just one of them? You can't—without unique identifiers, you risk updating or deleting the wrong records.
Primary keys solve this by giving each row an unambiguous identity. They're also essential for relationships between tables. When one table references another (like orders referencing customers), it uses the primary key to establish that connection reliably.
Common Primary Key Patterns
- Single auto-incrementing integer:
customer_id(1, 2, 3, ...) - Natural keys: Existing unique values like
emailorssn - Composite keys: Multiple columns together, like
(order_id, product_id)
The database enforces primary key constraints automatically, rejecting any attempt to insert duplicate or null values.
Key Takeaway: Primary keys are mandatory unique, non-null identifiers that ensure each table row can be precisely referenced, forming the foundation of database integrity and relationships between tables.