SQL Notes
How to use UNIQUE constraints in SQL to prevent duplicate values in columns, understand the difference between UNIQUE and PRIMARY KEY, and implement data integrity rules.
Think about email addresses in a user registration system. Every user must have a different email — no two accounts can share the same one. But email is not the primary key (you probably use UserID for that). So how do you enforce uniqueness on a non-primary-key column? That is exactly what the UNIQUE constraint does.
The UNIQUE constraint ensures that all values in a column or combination of columns are distinct across the entire table. Unlike PRIMARY KEY, you can have multiple UNIQUE constraints per table, and UNIQUE columns can contain NULL values in most databases.
In real-world applications, you frequently have multiple columns that must be unique beyond just the primary key. A users table has a unique UserID (primary key), but also needs unique email addresses, unique usernames, and possibly unique phone numbers. Each of these represents a different way to identify or contact a user, and allowing duplicates in any of them would create confusion and potential security issues. UNIQUE constraints handle all these requirements elegantly.
Basic Syntax
Inline Definition
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50) UNIQUE,
Email VARCHAR(255) UNIQUE,
Phone VARCHAR(15) UNIQUE
);Table-Level Definition
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50),
Email VARCHAR(255),
Phone VARCHAR(15),
UNIQUE (Username),
UNIQUE (Email),
UNIQUE (Phone)
);Named Constraints
Naming your constraints makes them easier to manage:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50),
Email VARCHAR(255),
Phone VARCHAR(15),
CONSTRAINT uq_username UNIQUE (Username),
CONSTRAINT uq_email UNIQUE (Email),
CONSTRAINT uq_phone UNIQUE (Phone)
);How UNIQUE Works in Practice
Let us see what happens when you try to insert duplicate values:
-- First insert: Works fine
INSERT INTO Users (UserID, Username, Email, Phone)
VALUES (1, 'rahul_dev', 'rahul@example.com', '9876543210');
-- Second insert: Different values, works fine
INSERT INTO Users (UserID, Username, Email, Phone)
VALUES (2, 'priya_code', 'priya@example.com', '9876543211');
-- FAILS: Duplicate email
INSERT INTO Users (UserID, Username, Email, Phone)
VALUES (3, 'amit_sql', 'rahul@example.com', '9876543212');
-- ERROR: Duplicate entry 'rahul@example.com' for key 'uq_email'
-- FAILS: Duplicate username
INSERT INTO Users (UserID, Username, Email, Phone)
VALUES (4, 'rahul_dev', 'amit@example.com', '9876543213');
-- ERROR: Duplicate entry 'rahul_dev' for key 'uq_username'UNIQUE and NULL Values
Here is where UNIQUE behaves differently from PRIMARY KEY. Most databases allow NULL in UNIQUE columns, but the rules vary:
PostgreSQL, SQL Server, SQLite
Allow multiple NULL values in a UNIQUE column because NULL is considered unknown, and two unknowns are not necessarily the same.
-- Both inserts work in PostgreSQL
INSERT INTO Users (UserID, Username, Email, Phone)
VALUES (5, 'guest1', 'guest1@example.com', NULL);
INSERT INTO Users (UserID, Username, Email, Phone)
VALUES (6, 'guest2', 'guest2@example.com', NULL);MySQL
Only allows one NULL value per UNIQUE column.
If you want to prevent NULLs entirely, combine UNIQUE with NOT NULL:
Email VARCHAR(255) NOT NULL UNIQUEComposite UNIQUE Constraints
You can enforce uniqueness across a combination of columns. The individual columns can repeat, but the combination must be unique:
CREATE TABLE CourseSchedule (
ScheduleID INT PRIMARY KEY AUTO_INCREMENT,
RoomNumber VARCHAR(10) NOT NULL,
DayOfWeek VARCHAR(10) NOT NULL,
TimeSlot VARCHAR(20) NOT NULL,
CourseID VARCHAR(10) NOT NULL,
CONSTRAINT uq_room_schedule UNIQUE (RoomNumber, DayOfWeek, TimeSlot)
);This ensures no two courses are scheduled in the same room at the same time on the same day:
-- Works: Different rooms
INSERT INTO CourseSchedule (RoomNumber, DayOfWeek, TimeSlot, CourseID)
VALUES ('R101', 'Monday', '9:00-10:00', 'CS201');
INSERT INTO CourseSchedule (RoomNumber, DayOfWeek, TimeSlot, CourseID)
VALUES ('R102', 'Monday', '9:00-10:00', 'MA101');
-- FAILS: Same room, same day, same time
INSERT INTO CourseSchedule (RoomNumber, DayOfWeek, TimeSlot, CourseID)
VALUES ('R101', 'Monday', '9:00-10:00', 'PH101');
-- ERROR: Duplicate entry 'R101-Monday-9:00-10:00'Real-World Example: Employee Management
CREATE TABLE Employees (
EmpID INT PRIMARY KEY AUTO_INCREMENT,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL,
EmployeeCode VARCHAR(20) NOT NULL,
AadharNumber CHAR(12),
PanNumber CHAR(10),
CONSTRAINT uq_emp_email UNIQUE (Email),
CONSTRAINT uq_emp_code UNIQUE (EmployeeCode),
CONSTRAINT uq_emp_aadhar UNIQUE (AadharNumber),
CONSTRAINT uq_emp_pan UNIQUE (PanNumber)
);Each employee has a unique email, employee code, Aadhar number, and PAN number. The Aadhar and PAN columns allow NULL for employees who have not yet submitted their documents.
Real-World Example: E-Commerce Platform
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
SKU VARCHAR(30) NOT NULL,
Barcode VARCHAR(50),
ProductName VARCHAR(200) NOT NULL,
Slug VARCHAR(200) NOT NULL,
CONSTRAINT uq_product_sku UNIQUE (SKU),
CONSTRAINT uq_product_barcode UNIQUE (Barcode),
CONSTRAINT uq_product_slug UNIQUE (Slug)
);The SKU is the internal identifier, Barcode is the manufacturer code, and Slug is the URL-friendly name. All three must be unique to avoid confusion.
Adding UNIQUE to an Existing Table
-- Add a single-column UNIQUE constraint
ALTER TABLE Employees
ADD CONSTRAINT uq_emp_phone UNIQUE (Phone);
-- Add a composite UNIQUE constraint
ALTER TABLE CourseSchedule
ADD CONSTRAINT uq_instructor_schedule UNIQUE (InstructorID, DayOfWeek, TimeSlot);Note: The ALTER will fail if existing data contains duplicates in those columns. Clean up duplicates first.
Dropping a UNIQUE Constraint
MySQL
ALTER TABLE Users DROP INDEX uq_email;PostgreSQL and SQL Server
ALTER TABLE Users DROP CONSTRAINT uq_email;UNIQUE Creates an Index
When you define a UNIQUE constraint, the database automatically creates an index on that column. This means queries that filter by a UNIQUE column are very fast:
-- This query uses the UNIQUE index for fast lookup
SELECT * FROM Users WHERE Email = 'rahul@example.com';You do not need to create a separate index on columns that already have UNIQUE constraints.
Common Mistakes to Avoid
Mistake 1: Confusing UNIQUE with PRIMARY KEY. Use PRIMARY KEY for row identification. Use UNIQUE for additional columns that must be distinct.
Mistake 2: Not handling duplicates before adding UNIQUE. Always check for existing duplicates before adding a UNIQUE constraint to an existing table:
-- Find duplicates before adding constraint
SELECT Email, COUNT(*)
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;Mistake 3: Assuming UNIQUE prevents NULL duplicates in all databases. Behavior varies. If NULLs must be prevented, always combine UNIQUE with NOT NULL.
Mistake 4: Using UNIQUE when CHECK or FOREIGN KEY would be better. UNIQUE prevents duplicates. If you need to restrict values to a specific set, use CHECK or a foreign key to a lookup table.
Best Practices
- Always name your UNIQUE constraints for easier management and clearer error messages
- Combine with NOT NULL for columns that must have a unique, non-empty value
- Use composite UNIQUE for business rules involving column combinations
- Check for duplicates before adding UNIQUE to existing tables
- Do not duplicate indexes — UNIQUE already creates one
- Use UNIQUE for natural keys like email, phone, SSN, employee code
Quick Summary
| Scenario | Use UNIQUE? |
|---|---|
| Email address | Yes |
| Phone number | Yes |
| Username | Yes |
| Government ID (Aadhar, PAN) | Yes |
| First name | No (duplicates expected) |
| City | No (duplicates expected) |
| Room + Time + Day combination | Yes (composite) |
The UNIQUE constraint is your tool for ensuring data integrity beyond the primary key. It guarantees that important identifiers and codes remain distinct across your entire table, preventing the data confusion that duplicates inevitably cause.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for UNIQUE Key Constraint in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, unique, key
Related SQL Complete Guide Topics