Course contentsShow
← Software Engineering
Lesson 872 of 1,93327. Database Design in PracticeFree lesson

Primary Key and Unique Indexes

Understanding how primary keys automatically create clustered indexes and unique constraints create unique indexes.

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.