Understanding Database Indexes

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

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.

How the Index Tracks Values

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.

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

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

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

• Creating a regular index when duplicates must be prevented

  A regular index can speed up queries but does not enforce uniqueness.

  Fix: Use CREATE UNIQUE INDEX for a column whose values must not repeat.

• Assuming an application check is enough

  The database itself will not block a later insert or update that creates a duplicate.

  Fix: Enforce the rule at the database level with a unique index.

• Treating every column as a uniqueness candidate

  A uniqueness constraint rejects values that the data model may legitimately need to repeat.

  Fix: Use the constraint when the business logic requires no duplicate values.

• Confusing a unique constraint with the primary key

  A table can have multiple unique constraints, while it has only one primary key.

  Fix: 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

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.