Primary Key
A unique identifier for each record in a database table, ensuring no two rows have the same value and enabling efficient data retrieval.
Detailed Explanation
A primary key is a column (or set of columns) that uniquely identifies every row in a table. It must contain unique values and cannot contain NULL values. Primary keys are the foundation of relational database design—they enable relationships between tables, efficient querying, and data integrity.
There are two types: natural keys (real-world identifiers like email or ISBN) and surrogate keys (generated identifiers like auto-incrementing integers or UUIDs). Most modern applications use surrogate keys because natural keys can change over time and may not always be available. When a primary key consists of multiple columns, it is called a composite primary key.
Why It Matters
Primary keys are fundamental to database design. They ensure data integrity, enable relationships between tables, and optimize query performance.
Real-World Example
In a users table, `id` (auto-incrementing integer or UUID) serves as the primary key. Each user has a unique id, and this id is referenced by other tables (orders, posts, comments) to establish relationships.
When to Use
Every database table should have a primary key. It is required for relational integrity and is automatically indexed for fast lookups.
Advantages
- Guarantees unique row identification
- Automatically creates an index for fast queries
- Enforces data integrity
- Required for foreign key relationships
- Optimizes JOIN performance
Disadvantages
- Surrogate keys add an extra column with no business meaning
- Composite primary keys can complicate queries
- UUID primary keys can fragment indexes
- Changing a primary key is expensive and risky
- Over-reliance on auto-increment can create bottlenecks in distributed systems
Related Terms
Frequently Asked Questions
Should I use UUIDs or auto-incrementing integers as primary keys?
UUIDs are better for distributed systems and prevent ID guessing. Auto-incrementing integers are simpler, faster to index, and easier to debug. For most applications, auto-incrementing integers work fine until you need distributed ID generation.
Can a table have multiple primary keys?
No. A table can only have one primary key, but it can be a composite key made up of multiple columns. You can have multiple unique constraints, but only one primary key.
What happens if I delete a row referenced by foreign keys?
It depends on the foreign key constraint. CASCADE deletes dependent rows, RESTRICT prevents deletion, SET NULL sets foreign keys to NULL, and SET DEFAULT sets them to their default value. Choose based on your data integrity requirements.
Why not use email as a primary key?
Emails can change, they are larger than integers (slower joins), and they expose PII in URLs. Use a surrogate key (id) as primary key and add a unique constraint on email separately.
Do primary keys need to be numeric?
No. Primary keys can be strings (UUIDs), timestamps, or any data type as long as they are unique and not null. However, numeric keys are generally faster for joins and indexing.