SQL Topics
UNIQUE
title: UNIQUE
The UNIQUE constraint guarantees that every value in a column (or group of columns) appears no more than once in the table.
It is useful for business-critical fields such as email addresses, phone numbers, or product codes that must not be duplicated across records.
What the UNIQUE Constraint Does
UNIQUE enforces distinctness at the database level.
Example:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE,
Phone VARCHAR(20) UNIQUE
);Attempting to register a second user with the same email fails immediately:
INSERT INTO Users (UserID, Email)
VALUES (1, 'rahul@example.com');
INSERT INTO Users (UserID, Email)
VALUES (2, 'rahul@example.com'); -- ERROR: Duplicate emailDefining a UNIQUE Constraint
Inline form:
Email VARCHAR(255) UNIQUETable constraint form:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(255),
UNIQUE (Email)
);The table constraint form allows composite unique constraints.
CREATE TABLE Contacts (
PersonID INT,
ContactType VARCHAR(50),
ContactValue VARCHAR(255),
UNIQUE (PersonID, ContactType)
);Here, a person can have an email and a phone, but not two emails.
UNIQUE vs PRIMARY KEY
| Property | UNIQUE | PRIMARY KEY |
|---|---|---|
| Allows NULL? | Yes (often only once, depending on database) | No |
| How many per table? | Multiple | One |
| Creates index? | Yes | Yes |
| Identifies row? | No | Yes |
Use a primary key to identify rows. Use UNIQUE to prevent duplicate values.
NULL and UNIQUE
Most databases allow a single NULL in a UNIQUE column, but allow only one NULL. Two rows with NULL in a UNIQUE column may raise an error depending on the engine.
Check your database documentation for exact behavior.
Dropping and Adding UNIQUE Constraints
ALTER TABLE Users
ADD UNIQUE (Email);ALTER TABLE Users
DROP UNIQUE (Email);Real-World Uses
| Field | Reason |
|---|---|
| Prevents duplicate accounts | |
| Phone | Prevents repeated contact numbers |
| Employee ID | Keeps identifiers distinct |
| Product SKU | Avoids duplicate inventory entries |
| Username | Stops login collisions |
Pseudocode example:
If User table has duplicate Emails:
For each user:
Print UserID and Email
Prevent new INSERT with duplicate EmailFrequently Asked Questions
Q: Can a table have multiple UNIQUE constraints? A: Yes. You can apply UNIQUE to as many columns or column groups as needed.
Q: Can a composite UNIQUE key allow duplicate sub-values? A: Yes. In the Contacts example above, the same person can have both ('1', 'email') and ('1', 'phone') because the combination differs.
Q: Does UNIQUE require an index? A: Yes. The database automatically creates a unique index to enforce the constraint.
Summary
- Use
UNIQUEto prevent duplicate values. - It works on single columns or column groups.
- It does not replace a
PRIMARY KEY. - Consider NULL handling when designing unique business rules.
Next Step
See NOT NULL to learn how to require values before building more complex schemas, or return to the SQL Constraints overview.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for UNIQUE.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, constraints, unique
Related SQL Topics