Transactions and Rollback Behavior
Because the transaction is rolled back, the attempted duplicate does not remain in the table.
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.
A uniqueness constraint protects data integrity by ensuring that all values in a protected column are unique.
Tracing a Rejected Insert
What do you think happens?
A table already contains the email value learner@example.com. An insert attempts to add another row with that same value, and the column has a uniqueness constraint. What happens?
Reveal answer
The database rejects the operation, rolls back the transaction, and returns an error.
The uniqueness check finds that the proposed value already exists. The operation is stopped before it completes, the transaction is rolled back so no change is made to the table, and an error indicates that the constraint was violated.
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
Protecting User Email Values
A users table has a uniqueness constraint on email. One existing row contains learner@example.com. A later insert proposes the same email.
- 11. Existing data
The unique index already tracks learner@example.com as a value belonging to an existing row.
- 22. Proposed insert
The new row proposes learner@example.com as its email value.
- 33. Index comparison
The database checks the proposed value against the values tracked by the unique index and finds a match.
- 44. Enforcement
Because the value already exists, the uniqueness constraint is violated and the insert is stopped before it completes.
- 55. Transaction result
The transaction is rolled back, so the attempted row is not added to the table. The database returns an error indicating that the constraint was violated.
The table keeps its original row, and the duplicate row is not stored.
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.
| Index form | Primary behavior | Duplicate values |
|---|---|---|
| Regular index | Speeds up queries | Does not prevent them |
| Unique index | Speeds up queries and enforces uniqueness | Blocked |
The UNIQUE keyword changes an index from query-supporting structure into a constraint-enforcing index.
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
Common mistake Assuming a regular index prevents duplicates
What it looks likeCreating an index without the UNIQUE keyword and expecting repeated values to be rejected
Why it failsA regular index speeds up queries but does not enforce uniqueness.
Do this insteadUse the UNIQUE keyword when the column must not contain duplicate values.
Common mistake Expecting the database to keep a duplicate and report it later
What it looks likeTreating the constraint as a cleanup process that runs after the insert
Why it failsThe database checks the unique index before the insert or update completes.
Do this insteadUnderstand the constraint as an immediate gatekeeper: a found value blocks the operation.
Common mistake Assuming rollback leaves the attempted row in the table
What it looks likeLooking for the duplicate after the database reports a uniqueness error
Why it failsRollback means no change is made to the table by the rejected operation.
Do this insteadUse the error to identify the violated constraint, then correct the proposed value or the operation.
Common mistake Treating every unique column as the primary key
What it looks likeAssuming a unique email column must be the table's main identifier
Why it failsA table can have multiple unique constraints, while it has only one primary key. They serve different roles.
Do this insteadDistinguish the table's main identifier from additional columns that must be unique because of business requirements.
Practice the Transaction Trace
A product table uses a unique index for product codes. The index already contains P-100. Trace what happens when an insert proposes P-100, and then explain what would happen if the insert proposed a product code not already in the index.
Hints
- First identify whether the proposed value is already tracked by the unique index.
- For a matching value, include the rejected operation, rollback, and error.
- For a value not found, include the proceeding insert and the index update.
Key Takeaways
- 1A uniqueness constraint ensures that protected column values do not duplicate one another.
- 2A unique index tracks indexed values and checks proposed inserts and updates before they complete.
- 3The UNIQUE keyword changes a regular index into a constraint-enforcing index.
- 4When a duplicate is detected, the database rejects the operation, rolls back the transaction, and returns an error.
- 5Unique constraints protect business-required values such as emails, usernames, ISBN numbers, and product codes, while remaining distinct from the table's primary key.
At a glance
- A uniqueness constraint prevents duplicate values by using a unique index as a database-level gatekeeper.
- The database checks the index before an insert or update completes.
- A matching indexed value causes rejection, rollback, and an error; a new value allows the change and updates the index.
- The UNIQUE keyword is required when a CREATE INDEX statement must enforce uniqueness rather than only improve query speed.
- Unique constraints support business rules and can coexist with a separate primary key.