Primary Keys: Identifying Rows Uniquely
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 email addresses. If two rows contain the same email address, an application may no longer know which row represents that user. A uniqueness constraint prevents this situation by requiring every value in the constrained column to be unique. The database checks the rule itself, so duplicate values are blocked even when they result from an application mistake.
What do you think happens?
A users table already contains the email value alex@example.com. What happens when another row tries to use that same value in a uniquely constrained email column?
Reveal answer
Answer: The database rejects the operation
The database checks the unique index before completing the insert or update. If the value already exists, the operation is blocked, the transaction is rolled back, and an error is returned.
The Index as Gatekeeper
A uniqueness constraint is enforced through a special index. The index keeps track of the values in the constrained column and the row locations associated with those values. When an insert or update introduces a value, the database checks the index before allowing the change. If the value is already present, the constraint is violated. If the value is not present, the operation proceeds and the index is updated with the new value.
The index does more than speed up a query in this situation. With uniqueness enforcement enabled, it becomes a gatekeeper: it checks every relevant insert and update and prevents a value from appearing more than once.
Following Values to Rows
Think of the unique index as a collection of unique column values paired with the locations of their rows. For example, an index on email can associate alex@example.com with the row for Alex and sam@example.com with the row for Sam. If a new row supplies alex@example.com, the database finds that value in the index and knows that the value is already associated with a row. The duplicate is therefore rejected.
Adding UNIQUE to an Index
The UNIQUE keyword is optional in a CREATE INDEX statement. Without UNIQUE, the index helps speed up queries but does not prevent duplicate values. Including UNIQUE changes the index into a constraint-enforcing index. The database then applies the uniqueness rule automatically to future insert and update operations involving that column.
When a column must never contain duplicates because of a business rule, use a uniqueness constraint rather than relying only on application checks. The database then protects the rule for every insert and update operation that affects the column.
Primary Key or Unique Column
A primary key and a uniqueness constraint both involve uniqueness, but they are not the same role. A primary key is the table's main identifier and is used to reference rows from other tables. A table can have only one primary key. A table can have multiple unique constraints that protect additional columns from duplicates.
| Database feature | Purpose | How many can a table have? | Source example |
|---|---|---|---|
| Primary key | Main identifier for the table's rows and a value used to reference rows from other tables | One | user_id |
| Uniqueness constraint | Prevents duplicate values in a column because of a business requirement | Multiple |
Choosing Columns That Must Be Unique
Use a uniqueness constraint whenever the business logic requires that a column contain no duplicate values. The source 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 rule protects data integrity by catching data-entry errors or application bugs that might otherwise create duplicates.
Protecting User Email Addresses
A users table has user_id as its main identifier. The application requires every user to have a different email address.
Identify the business rule: The email column must not contain duplicate values, so it needs a uniqueness constraint.
Keep the primary key role separate: user_id remains the table's main identifier, while email receives a separate uniqueness rule.
Let the database enforce the rule: A unique index on email checks future inserts and updates. If an email already exists, the attempted change is rejected and the transaction is rolled back.
The table can have one primary key on user_id and an additional uniqueness constraint on email.
Common Design Mistakes
Assuming that every regular index prevents duplicates.
The UNIQUE keyword is what transforms a regular index into a constraint-enforcing index.
Fix:
Use the UNIQUE keyword when the column must contain no duplicate values.Treating every unique column as the primary key.
A primary key is the table's main identifier, while a table can have multiple additional unique constraints.
Fix:
Choose the primary key for the table's main row-identity role and use uniqueness constraints for separate business rules.Relying only on application validation to detect duplicates.
The database itself must enforce the rule to protect data integrity during inserts and updates.
Fix:
Create a unique index so the database checks the value before allowing the change.Ignoring the result of a rejected operation.
The database returns an error and rolls back the transaction, so the attempted change was not made.
Fix:
Handle the database error and treat the operation as unsuccessful.
Check Your Understanding
A books table stores ISBN values. The same ISBN must not appear in two rows, but the table already has a different primary key. Explain which database rule should be added, what the unique index should check, and what should happen if an insert repeats an existing ISBN.
Hints
- The rule is based on preventing duplicate values in one column.
- A table may have multiple uniqueness constraints but only one primary key.
- The database checks the index before completing the insert.
- A uniqueness constraint requires every value in a column to be different. A unique index stores the column's values and row locations and checks new inserts or updates before they complete. The UNIQUE keyword makes a CREATE INDEX statement enforce this rule; without it, the index does not prevent duplicates. A uniqueness constraint is separate from a primary key, so a table can use one primary key and several additional unique constraints. Apply the rule whenever business logic requires values such as emails, usernames, ISBNs, or product codes to remain unique.
Key Takeaways
- A uniqueness constraint prevents duplicate values in a column.
- The database enforces the rule through a unique index that checks inserts and updates.
- The UNIQUE keyword changes a regular index into a constraint-enforcing index.
- A primary key is the table's main identifier, while additional unique constraints protect other business rules.
- Use uniqueness constraints for values such as emails, usernames, ISBNs, and product codes when duplicates would damage data integrity.