UNIQUE Constraint
UNIQUE ensures no two rows have the same value in a column or combination of columns. Unlike PRIMARY KEY, UNIQUE columns can contain NULL (up to one NULL per column in most databases). Use UNIQUE for email addresses, usernames, phone numbers, and other natural keys.
UNIQUE Constraint Details
- Prevents duplicate values in a column
- Creates an index automatically - fast lookups on that column
- Allows one NULL (NULL is not equal to NULL for uniqueness purposes)
- Can be composite: UNIQUE(col1, col2) - unique combination
- A table can have multiple UNIQUE constraints
- UNIQUE is automatically implied by PRIMARY KEY
UNIQUE Examples
-- Unique email in users table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE, -- no two users share an email
name VARCHAR(100)
);
-- Composite unique: same product can't appear twice in same order
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
UNIQUE(order_id, product_id) -- unique pair
);
-- Named unique constraint
ALTER TABLE users
ADD CONSTRAINT uq_user_email UNIQUE (email);
-- Violation:
INSERT INTO users (email, name) VALUES ('john@example.com', 'John'); -- OK
INSERT INTO users (email, name) VALUES ('john@example.com', 'Jane'); -- ERROR!
-- duplicate key value violates unique constraint "users_email_key"
-- Handle conflicts gracefully (PostgreSQL INSERT ... ON CONFLICT)
INSERT INTO users (email, name)
VALUES ('john@example.com', 'Updated Name')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
-- UPSERT: insert or update if email already existsQuick Quiz
Tip
Tip
Practice UNIQUE Constraint in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Constraints enforce data integrity at DB level.
Common Mistake
Warning
A common mistake with UNIQUE Constraint is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of UNIQUE Constraint from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- UNIQUE ensures no two rows have the same value in a column or combination of columns.
- Prevents duplicate values in a column
- Creates an index automatically - fast lookups on that column
- Allows one NULL (NULL is not equal to NULL for uniqueness purposes)