Understanding Database Indexes

A uniqueness constraint ensures all values in a column are unique by using a special index to detect and block duplicates.

Programming

Interactive lab

Try it: Keys and Constraints

How SQLite enforces PRIMARY KEY, UNIQUE, NOT NULL and FOREIGN KEY constraints on INSERT and DELETE, how an INTEGER PRIMARY KEY assigns ids (with and without AUTOINCREMENT), what INSERT OR IGNORE skips, and what ON DELETE RESTRICT, CASCADE and SET NULL do to child rows.

How it works

INSERT: a NULL id is replaced by max(id) + 1 — or, with AUTOINCREMENT, by one more than the largest id ever used — and last_insert_rowid() reports it.
Constraints are checked in order: NOT NULL, then the PRIMARY KEY, then UNIQUE, then the FOREIGN KEY (the parent row must exist). The first failure aborts the statement with SQLite's error message.
INSERT OR IGNORE silently skips a row that breaks NOT NULL, PRIMARY KEY or UNIQUE — but a foreign-key failure is still an error.
DELETE of a parent row that has children: RESTRICT refuses, CASCADE deletes the children too, SET NULL clears their foreign key. None of this happens unless PRAGMA foreign_keys = ON (SQLite's default is OFF).

Default run (5 steps): 3 artists and 3 albums. PRAGMA foreign_keys = ON, ON DELETE RESTRICT. Choose an operation. Example: INSERT INTO artist (name) VALUES ('Queen'). … Error: UNIQUE constraint failed: artist.name. Nothing was changed (the statement is rolled back).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Two fixed tables of at most 8 rows and ids up to 99; changing the schema recreates the tables with the starting rows. Error texts and ids were checked step by step against sqlite3.

Educational simulation

Loading the simulation…

Why Duplicate Values Matter

A database can contain columns where duplicate values are acceptable, such as a column recording a category or status. Other columns represent values that should identify one specific record, such as an email address, username, ISBN number, or product code. If two rows contain the same value in one of these columns, the data may no longer match the rules of the application.

A uniqueness constraint is a database rule that requires every value in a protected column to be unique. The database enforces this rule through a special index. The index keeps track of the values already present and prevents an insert or update when its value is already there.

The Duplicate-Check Path

What happens when a new row contains a value that already exists in a column protected by a unique index?

The database checks the incoming value against the unique index before completing the insert or update. If the value is already present, the constraint is violated, so the database blocks the operation. It rolls back the transaction, meaning the attempted change is not made to the table, and returns an error to the application.

What do you think happens?

A users table already contains the email address member@example.com. A second row attempts to use the same email address. What happens?

The second row is inserted normally
The database replaces the first row
The operation is rejected and the transaction is rolled back
The index stores both rows without reporting a problem

Reveal answer

Answer:

The operation is rejected and the transaction is rolled back.

A unique index detects that the incoming value already exists. The database blocks the insert or update, makes no table change from that operation, and returns an error indicating that the uniqueness constraint was violated.

How the Index Tracks Values

How does the database map each column value to an index entry and identify whether that value is already present?

When a unique index is created, the database builds a special data structure containing the values in the protected column and their row locations. The collection can be ordered or hashed. Its purpose is to make duplicate detection quick: the database looks for the incoming value in the index rather than treating the whole table as an unexamined list.

If the value is found, the database knows that allowing another occurrence would violate the rule. If the value is not found, the insert or update can proceed, and the new value is added to the index. The index therefore works as a gatekeeper for changes to the protected column.

Creating the Uniqueness Rule

The UNIQUE keyword changes the role of an index. A regular CREATE INDEX statement creates an index that speeds up queries but does not prevent duplicate values. Adding UNIQUE creates a constraint-enforcing index, so the database checks the protected column during future insert and update operations.

What changes in the database's behavior when CREATE INDEX is replaced with CREATE UNIQUE INDEX?

sql

CREATE INDEX users_email_index ON users (email);

CREATE UNIQUE INDEX users_email_unique_index ON users (email);

CREATE INDEX users_email_index ON users (email);

CREATE UNIQUE INDEX users_email_unique_index ON users (email);

In the first statement, the index does not prevent repeated email values. In the second statement, UNIQUE tells the database that repeated email values are not allowed. The second form is the appropriate choice when the application requires every email value in the column to be different.

Worked Email Example

Worked example

Protecting User Email Addresses

A users table has a user_id column and an email column. Each user should have a different email address. What should happen when a new row uses an email address already stored in the table?

1
Choose the protected column
The email column represents a value that the business rule requires to be unique.
2
Create a unique index
Use CREATE UNIQUE INDEX for the email column. The database builds an index that tracks the email values and their row locations.
3
Attempt the insert
When a new row arrives, the database checks the incoming email against the unique index before completing the insert.
4
Handle a duplicate
If the email is already in the index, the database rejects the operation, rolls back the transaction, and returns an error.

Result

The table remains protected from duplicate email addresses even when an insert or update attempts to introduce one.

The same reasoning applies to usernames, ISBN numbers, and product codes when the surrounding business rule requires one occurrence of each value. The important design step is not simply finding a column that looks important; it is identifying a column whose duplicate values would violate the rules of the data.

Unique Constraint or Primary Key

Feature	Uniqueness constraint	Primary key
Purpose	Protects a column from duplicate values based on a business requirement	Acts as the table's main identifier
How many a table can have	A table can have multiple unique constraints	A table can have only one primary key
Uniqueness	The protected values must be unique	A primary key is always unique
Example role	An email value that must not be shared by two users	A user_id used to identify and reference rows

A unique column and a primary key can both contain unique values while serving different design purposes.

A uniqueness constraint is not a replacement for understanding the table's main identifier. A table might use user_id as its primary key and also protect email with a unique constraint. Both columns must contain unique values, but user_id identifies rows as the table's main identifier while email is protected because the application's business rules require it to be unique.

Mistakes with Uniqueness Rules

Common mistake
Creating a regular index when duplicates must be prevented
What it looks like
```
CREATE INDEX users_email_index ON users (email);
```
Why it fails
A regular index can speed up queries but does not enforce uniqueness.
Do this instead
Use CREATE UNIQUE INDEX for a column whose values must not repeat.
Common mistake
Assuming an application check is enough
What it looks like
The application checks whether an email exists, but the database has no uniqueness constraint.
Why it fails
The database itself will not block a later insert or update that creates a duplicate.
Do this instead
Enforce the rule at the database level with a unique index.
Common mistake
Treating every column as a uniqueness candidate
What it looks like
Applying a uniqueness rule to a column where repeated values are valid.
Why it fails
A uniqueness constraint rejects values that the data model may legitimately need to repeat.
Do this instead
Use the constraint when the business logic requires no duplicate values.
Common mistake
Confusing a unique constraint with the primary key
What it looks like
Assuming a table can have only one unique constraint because it can have only one primary key.
Why it fails
A table can have multiple unique constraints, while it has only one primary key.
Do this instead
Use the primary key for the table's main identifier and additional unique constraints for other protected columns.

Choosing Protected Columns

Choose a uniqueness constraint when a duplicate would represent invalid data rather than merely an inconvenient query result. Email addresses in a users table, usernames, ISBN numbers, and product codes are examples of values that may require this protection. In each case, the constraint expresses a rule about the data itself.

Protect email when each user should have a different email address.
Protect usernames when each user should have a different login name.
Protect ISBN numbers when each book identifier should occur only once.
Protect product codes when each inventory code should identify one product.
Do not add uniqueness merely because a column is frequently queried; a regular index may be sufficient when duplicate values are valid.

Check Your Design

EASY

A books table contains book_id, title, author, and isbn. The design requires each ISBN to appear only once, while many books may share the same author. Which column should receive a unique index, and why should author not receive one?

Hints

Ask which value identifies one book rather than a group of books.
A uniqueness constraint should be used when duplicate values would violate the data rule.

Your attempt (not saved)

Worked example

Practice Answer

Choose the column that must not contain duplicate values.

1
Inspect the ISBN column
The design requires each ISBN to occur only once, so duplicate ISBN values would violate the stated rule.
2
Inspect the author column
Multiple books can legitimately have the same author, so duplicate author values are allowed.
3
Apply the rule
Create a unique index on isbn, not on author.

Result

The isbn column should receive the uniqueness constraint because its values must be unique; author should not because repeated authors are valid.

Key Takeaways

1
A uniqueness constraint requires all values in a protected column to be different.
2
A unique index tracks column values and their row locations so duplicates can be detected during inserts and updates.
3
CREATE UNIQUE INDEX enforces uniqueness, while CREATE INDEX alone does not prevent duplicate values.
4
When a duplicate violates the constraint, the database rejects the operation, rolls back the transaction, and returns an error.
5
A table can have multiple unique constraints but only one primary key; use uniqueness constraints for additional business rules.

At a glance

A uniqueness constraint protects a column from duplicate values.
The database enforces the rule through a unique index that checks inserts and updates.
The UNIQUE keyword distinguishes a constraint-enforcing index from a regular query index.
Violating the rule causes the database to reject the operation and roll back the transaction.
Use uniqueness constraints for values such as emails, usernames, ISBN numbers, or product codes when duplicates would violate the data model.

Related concepts

More concepts starting with U