SQL UNIQUE – Enforce Distinct Column Values
Introduction – What is SQL UNIQUE?
The UNIQUE constraint in SQL is used to ensure all values in a column (or set of columns) are distinct across rows. It helps maintain data integrity and prevent duplicates.
In this guide, you’ll learn:
- How to apply the
UNIQUEconstraint - Single-column vs multi-column unique indexes
- Differences from
PRIMARY KEY
1. Basic UNIQUE Constraint Syntax
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
Ensures no two users have the same email address.
2. Add UNIQUE with ALTER TABLE
ALTER TABLE customers
ADD CONSTRAINT unique_email UNIQUE (email);
Adds a named constraint after table creation.
3. Composite UNIQUE Constraint
CREATE TABLE bookings (
room_id INT,
booking_date DATE,
UNIQUE (room_id, booking_date)
);
Prevents double-booking a room on the same date.
4. UNIQUE vs PRIMARY KEY
| Feature | PRIMARY KEY | UNIQUE Constraint |
|---|---|---|
| Allows NULLs | No | Yes (one or more) |
| Max per table | 1 | Multiple allowed |
| Implies index | Yes | Yes |
Use PRIMARY KEY for entity identity, UNIQUE for alternate keys.
5. Behavior Notes
- Attempting to insert a duplicate value triggers an error:
-- Error:
INSERT INTO users (email) VALUES ('test@example.com'); -- if already exists
- Can be added inline or at the end of a
CREATE TABLEblock - Index is automatically created to enforce uniqueness
Best Practices
| Recommended | Avoid This |
|---|---|
Use UNIQUE for natural keys (email, username) | Overusing UNIQUE on optional data |
| Name your constraints explicitly | Relying on auto-named constraints |
| Use composite keys wisely | Assuming uniqueness applies to parts only |
Summary – Recap & Next Steps
The UNIQUE constraint is an essential tool for preserving data integrity by ensuring that certain column values remain distinct. It supports both single and composite key enforcement.
Key Takeaways:
- Prevents duplicate values in one or more columns
- Allows NULLs (unlike
PRIMARY KEY) - Ideal for alternate keys and natural identifiers
Real-World Relevance:
Used in email validation, registration systems, order codes, and inventory tracking.
FAQ – SQL UNIQUE
What does the UNIQUE constraint do?
Ensures all values in the column(s) are distinct.
Can I have multiple UNIQUE constraints on one table?
Yes. Each one can apply to different column(s).
Can UNIQUE columns be NULL?
Yes. But only one NULL is allowed in most databases.
How is UNIQUE different from PRIMARY KEY?
PRIMARY KEY = 1 per table, no NULLs. UNIQUE = multiple allowed, NULLs permitted.
Share Now :
