Data Integrity and Constraints
A uniqueness constraint ensures all values in a column are unique by using a special index to detect and block duplicates.
The Duplicate-Value Problem
Suppose a users table stores an email address for every user. If the same email address is stored for two different users, the database may no longer represent the rule that each user should have a distinct email. A uniqueness constraint protects this rule at the database level. It ensures that values in a constrained column are unique and blocks an insert or update when the value already exists.
What do you think happens?
A unique index already contains alex@example.com. What should happen when another row tries to store alex@example.com?
Reveal answer
Answer: The operation is rejected and the transaction is rolled back
When a duplicate is detected, the database stops the operation before it completes, rolls back the transaction, and returns an error indicating that the uniqueness constraint was violated.
The Unique Index as Gatekeeper
A uniqueness constraint is enforced through a special index. When the database creates a unique index on a column, the index keeps track of every value in that column and the locations of the rows containing those values. The index maintains an ordered or hashed collection of unique values and their row locations.
During an insert or update, the database checks the unique index before allowing the change. If the incoming 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 the new value. The source describes this lookup as occurring in near-constant time, so the index can act as an immediate gatekeeper rather than waiting until after the row has been stored.
Declaring Uniqueness with CREATE INDEX
The UNIQUE keyword in a CREATE INDEX statement tells the database to enforce uniqueness for the indexed column. Including UNIQUE transforms a regular index into a constraint-enforcing index. Without UNIQUE, an index can speed up queries but does not prevent duplicate values.
Protecting User Email Addresses
A users table should not contain two rows with the same email address. Decide what database rule is needed and identify the result of a duplicate insert.
Choose the protected column: The email column is the value that must not be duplicated because the business requirement is that each user should have a unique email address.
Create a unique index: Use CREATE INDEX with the UNIQUE keyword for the users email column. The UNIQUE keyword makes the index enforce the no-duplicates rule.
Check a new operation: Before an insert or update completes, the database checks the incoming email against the values tracked by the unique index.
Handle a duplicate: If the email is already present, the database rejects the operation, rolls back the transaction, and returns an error.
The table remains unchanged after the attempted duplicate operation, so the uniqueness rule protects the data even if an application sends an invalid duplicate.
Choosing Columns for Uniqueness
Use a uniqueness constraint when the business logic requires that a column have no duplicate values. The rule should describe a real requirement, not merely a preference for distinct-looking data. Examples from the source include email addresses in a users table, usernames, ISBN numbers in a books table, and product codes in an inventory system.
| Database design choice | Meaning | Effect on duplicate values |
|---|---|---|
| Unique constraint | A specific column must contain no duplicate values | Duplicate insert or update is rejected |
| Regular index | An index is created without the uniqueness rule | Duplicates are not prevented by the index |
| Primary key | The table's main identifier | The primary key is unique, and a table can have only one primary key |
Mistakes That Weaken Data Integrity
Creating a regular index when the requirement is uniqueness
A regular index can speed up queries, but it does not prevent duplicate values.
Fix:
Include UNIQUE in the CREATE INDEX statement when the column must contain no duplicates.Assuming the application alone can protect uniqueness
The database-level constraint is the safety net that catches data entry errors or application bugs that might otherwise allow duplicates.
Fix:
Enforce the requirement through a unique index so insert and update operations are checked by the database.Treating every unique column as the primary key
A table can have multiple unique constraints, but only one primary key. A primary key is the table's main identifier and is used to reference rows from other tables.
Fix:
Keep the primary-key role separate from additional uniqueness rules based on business requirements.Expecting a rejected duplicate operation to partially remain in the table
When a uniqueness violation occurs, the operation is stopped, the transaction is rolled back, and an error is returned.
Fix:
Handle the returned error in the application and understand that the violating change was not completed.
Practice the Gatekeeper Process
A books table stores ISBN numbers. The database design requires that each ISBN identify only one book. Explain why a unique index is appropriate, identify what the database checks before an insert or update, and describe what happens if the incoming ISBN is already present.
Hints
- Start with the business requirement: the ISBN must not be duplicated.
- The database checks the value against the unique index before allowing the operation.
- A duplicate causes rejection, rollback, and an error.
Checking the ISBN Rule
A new book is submitted with an ISBN that is already tracked by the unique index.
Identify the rule: ISBN numbers are an example of values that may need to be unique in a books table.
Perform the index check: The database looks for the incoming ISBN in the unique index before completing the insert or update.
Reject the duplicate: Because the value is already present, the database blocks the operation.
Protect the table: The transaction is rolled back and an error is returned, so the duplicate value is not stored.
The unique index prevents the books table from containing two rows with the same ISBN.
Key Takeaways
- A uniqueness constraint ensures that values in a constrained column are not duplicated.
- The database enforces the rule through a special unique index that tracks values and their row locations.
- The UNIQUE keyword in CREATE INDEX changes a regular index into a constraint-enforcing index.
- Before an insert or update completes, the database checks the unique index; a duplicate causes rejection, rollback, and an error.
- Unique constraints protect business requirements such as distinct email addresses, usernames, ISBN numbers, and product codes, while remaining distinct from the table's single primary key.
Key Takeaways
- Uniqueness constraints prevent duplicate values in columns where the business rule requires distinct data.
- A unique index checks incoming values during insert and update operations before the database accepts the change.
- The UNIQUE keyword in a CREATE INDEX statement is what makes the index enforce uniqueness.
- A duplicate causes the operation to be rejected, the transaction to be rolled back, and an error to be returned.
- A table may have multiple unique constraints but only one primary key.