Introduction
A primary key can look harmless when a table contains a few hundred rows. An email address identifies each customer, inserts complete quickly, and related tables work as expected.
As the database grows, that choice can become expensive. Wide values, changing business identifiers, and unsuitable insertion patterns can increase storage and maintenance costs. These problems usually accumulate gradually rather than causing an immediate failure.
Uniqueness Is Only the Starting Point
A primary key must uniquely identify each row and cannot contain NULL. However, satisfying those rules does not automatically make it a good design choice.
Consider these possible customer identifiers:
Candidate | Potential concern |
|---|---|
Customer name | Different customers can share a name |
Email address | Can change and requires a clear uniqueness policy |
Long business code | Can increase key storage and comparison costs |
Generated numeric ID | Requires a suitable generation strategy and sufficient range |
UUID | Requires attention to representation and generation pattern |
A useful primary key should be unique, stable, appropriately sized, and compatible with how records are created.
Wide Keys Can Spread Their Cost
Suppose an application uses a long customer identifier as its primary key. Related tables such as orders, payments, and support_tickets also store that identifier as a foreign key.
The cost is repeated across those relationships. Wider keys can mean:
More storage for referencing columns and their indexes.
Fewer index entries fitting into each page.
More memory needed to cache the same number of entries.
More data processed during comparisons and joins.
Some storage designs also include the clustered key in secondary-index entries. When the primary key supplies that clustered key, its width can affect several indexes, including the primary-key index.
The exact amplification depends on the database’s storage organization. A primary key does not universally determine how table records are stored.
Changing Keys Make Relationships Harder
Using an email address as a primary key connects row identity to a value that may change.
If a customer updates their email, every referencing relationship must remain consistent. Foreign-key rules may reject the update or propagate it, depending on the schema. Either way, changing an identifier used throughout the database adds work.
A stable internal identifier separates identity from contact information:
CREATE TABLE customers ( customer_id BIGINT PRIMARY KEY, email VARCHAR(254) NOT NULL UNIQUE, customer_name VARCHAR(100) NOT NULL);A related table can reference the stable ID:
CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));Here, changing an email does not require changing orders.customer_id.
The application must supply the IDs in these examples. Automatic ID-generation syntax depends on the database.
Also, UNIQUE(email) still matters if the business requires distinct email addresses. Adding an internal ID does not replace business constraints.
Changing email vs stable customer id
Insertion Order Affects Tree Indexes
In a B+ Tree index, new entries must fit into their key positions.
Increasing keys tend to direct inserts toward one end of the tree. Randomly distributed keys spread inserts across different leaf pages, potentially increasing the active working set.
When a target page lacks room, a split may be needed. Splits require additional maintenance and can leave pages less densely filled.
However, sequential keys are not automatically best. Many concurrent inserts targeting the same end of an index can create contention.
Random keys are not automatically wrong either. Distributed systems may benefit from independently generated identifiers. Choose according to the workload rather than a blanket rule.
Sequential vs random index insert
Natural Keys vs Surrogate Keys
A natural key comes from the business data. A surrogate key is introduced specifically to identify records.
Choice | Useful when | Main consideration |
|---|---|---|
Natural key | A compact, stable business identifier already exists | Business rules may change |
Surrogate key | Identity should remain independent of business attributes | Additional uniqueness constraints may be needed |
Composite key | A combination naturally identifies a relationship | All key columns must be carried by references |
For example, (student_id, course_id) can be a suitable primary key for an enrollment table when each student can enroll in a course only once.
A composite key is not inherently poor design. Its meaning, width, and referencing patterns determine whether it fits.
Choosing a Primary Key Deliberately
Before selecting a key, ask:
Will it remain stable? Avoid tying identity to frequently changing attributes.
Is its range sufficient? Allow room for expected growth.
Is its representation compact? Avoid unnecessary text or oversized combinations.
How will IDs be generated? Consider concurrency and distributed creation.
Where will it be referenced? Include related tables and indexes in the assessment.
How does the database store it? Check whether it also controls clustered organization.
Test realistic data volumes and insertion patterns. Changing a widely referenced primary key later can require coordinated schema changes, data migration, and application updates.
Summary
An unsuitable primary key can quietly increase storage, index maintenance, and relationship-management costs. Uniqueness is essential, but stability, width, and insertion patterns also matter.
There is no universally correct key type. Choose an identifier that fits the business rules and workload, preserve necessary business constraints, and evaluate its effects across the whole schema.
Be the first to add a comment.