Primary Key and Unique Indexes
What you'll learn: How primary keys and unique constraints automatically create indexes that enforce uniqueness and speed up data retrieval.
When you define a primary key or unique constraint in a database, you're not just adding a rule—you're also creating an index behind the scenes. Understanding this automatic behavior helps you make smarter design decisions.
Primary Keys Create Clustered Indexes
When you designate a column (or columns) as a primary key, most databases automatically create a clustered index on it. A clustered index determines the physical order of data in the table—think of it like organizing books alphabetically by title on a shelf. There can only be one clustered index per table because data can only be physically sorted one way.
CREATE TABLE users (
user_id INT PRIMARY KEY, -- Automatically creates clustered index
username VARCHAR(50),
email VARCHAR(100)
);
The user_id primary key automatically creates a clustered index, making lookups by user_id extremely fast and ensuring no duplicate values exist.
Unique Constraints Create Non-Clustered Indexes
When you add a UNIQUE constraint, the database creates a non-clustered index (also called a secondary index). This is like creating a separate lookup table—an index in the back of a textbook that points to page numbers without changing the book's organization.
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email); -- Creates non-clustered unique index
This unique index both enforces that no two users can share an email and speeds up queries that search by email.
Why This Matters
You don't need to manually create indexes for primary keys or unique constraints—the database does it automatically. However, you should still be aware that these indexes exist, consume storage, and slow down writes while speeding up reads.
Key Takeaway: Primary keys automatically create clustered indexes that physically organize table data, while unique constraints create non-clustered indexes that enforce uniqueness and accelerate lookups—both happen without explicit index creation commands.