Primary Keys and Entity Integrity
What you'll learn: How primary keys ensure every record in your database is uniquely identifiable and prevent duplicate or missing identifiers.
What is a Primary Key?
A primary key is a column (or set of columns) that uniquely identifies each row in a table. Think of it like a social security number or employee ID — no two people can have the same one, and everyone must have one.
In the relational model we just learned about, every table needs a way to distinguish one record from another. The primary key serves this critical role.
Entity Integrity Rule
Entity integrity is a fundamental database principle: the primary key of a table must never be null or duplicated.
This means:
- No nulls allowed: Every record must have a valid identifier
- No duplicates allowed: Each identifier must be unique across all records
Without this rule, you couldn't reliably reference specific records, and your data relationships would break down.
Why This Matters in System Design
When you're building systems at scale (remembering our lessons on sharding and replication), primary keys become even more critical:
- Shard routing often uses the primary key to determine which shard stores a record
- Replication relies on primary keys to identify which records to sync
- Foreign keys (relationships between tables) reference primary keys to maintain referential integrity
- Preventing duplicate writes becomes possible when you can uniquely identify each entity
Simple Example
Think of a users table. Without a primary key, you might have two "John Smith" records with no way to tell them apart. With a primary key like user_id, you can definitively say "user 12345 is this John Smith, and user 67890 is that John Smith."
Key Takeaway: Primary keys uniquely identify every record in a table and enforce entity integrity by preventing null or duplicate identifiers — essential for maintaining data consistency across distributed systems.