Transactions and Rollback Behavior

The Duplicate-Value Problem

A database often needs to guarantee that a particular column never contains duplicate values. For example, an application may require every user to have a different email address or username. A uniqueness constraint turns that business requirement into a database-enforced rule. Instead of relying only on application code, the database checks every relevant insert and update and blocks values that would duplicate an existing value.

Tracing a Rejected Insert

The important point is that rejection and rollback are connected. The database does not first store the duplicate and then clean it up. It stops the operation before it completes. Because the transaction is rolled back, the attempted duplicate does not remain in the table.

How the Unique Index Enforces the Rule

Uniqueness constraints are enforced through indexes. A unique index keeps track of every value in the protected column and the row location associated with each value. When an insert or update proposes a new value, the database checks the unique index before allowing the change. If the value is already present, the constraint is violated and the operation is blocked. If the value is not present, the operation proceeds and the index is updated to include it.

The index acts as a gatekeeper. It maintains an ordered or hashed collection of unique values and their row locations. The database queries this collection when a new value arrives. A found value means the proposed insert or update must be blocked; an unfound value allows the change and causes the new value to be added to the index.

Following the Insert Data Flow

Adding Uniqueness to an Index

The UNIQUE keyword is optional in a CREATE INDEX statement. Without UNIQUE, the index speeds up queries but does not prevent duplicate values. With UNIQUE, the index becomes a constraint-enforcing index: the database checks the indexed column during future insert and update operations and blocks a value that is already present.

Choosing Protected Columns

Use a uniqueness constraint when the business logic requires a column to contain no duplicate values. Examples include email addresses in a users table, usernames, ISBN numbers in a books table, and product codes in an inventory system. In each case, the constraint provides a database-level safety net against data-entry errors or application bugs that could otherwise introduce duplicates.

Mistakes with Rollback and Uniqueness

• Assuming a regular index prevents duplicates

  A regular index speeds up queries but does not enforce uniqueness.

  Fix: Use the UNIQUE keyword when the column must not contain duplicate values.

• Expecting the database to keep a duplicate and report it later

  The database checks the unique index before the insert or update completes.

  Fix: Understand the constraint as an immediate gatekeeper: a found value blocks the operation.

• Assuming rollback leaves the attempted row in the table

  Rollback means no change is made to the table by the rejected operation.

  Fix: Use the error to identify the violated constraint, then correct the proposed value or the operation.

• Treating every unique column as the primary key

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

  Fix: Distinguish the table's main identifier from additional columns that must be unique because of business requirements.

Practice the Transaction Trace

Key Takeaways

1. A uniqueness constraint ensures that protected column values do not duplicate one another.
2. A unique index tracks indexed values and checks proposed inserts and updates before they complete.
3. The UNIQUE keyword changes a regular index into a constraint-enforcing index.
4. When a duplicate is detected, the database rejects the operation, rolls back the transaction, and returns an error.
5. Unique constraints protect business-required values such as emails, usernames, ISBN numbers, and product codes, while remaining distinct from the table's primary key.