SQL - UNIQUE Constraint
• UNIQUE constraints prevent duplicate values in columns while allowing NULL values (unlike PRIMARY KEY), making them essential for enforcing business rules on alternate keys like email addresses,...
Key Insights
• UNIQUE constraints prevent duplicate values in columns while allowing NULL values (unlike PRIMARY KEY), making them essential for enforcing business rules on alternate keys like email addresses, usernames, or product codes. • Composite UNIQUE constraints can span multiple columns, enforcing uniqueness only when the combination of all columns matches, enabling complex data integrity rules. • UNIQUE constraints automatically create indexes, improving query performance but adding overhead to INSERT and UPDATE operations that must be evaluated during constraint design.
Understanding UNIQUE Constraints
A UNIQUE constraint ensures that all values in a column or set of columns are distinct across rows. Unlike PRIMARY KEY constraints, UNIQUE constraints allow NULL values, and a table can have multiple UNIQUE constraints. When you create a UNIQUE constraint, the database automatically creates a unique index to enforce the constraint efficiently.
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
username VARCHAR(50) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Attempting to insert duplicate email fails
INSERT INTO users (user_id, email, username)
VALUES (1, 'john@example.com', 'john_doe');
INSERT INTO users (user_id, email, username)
VALUES (2, 'john@example.com', 'jane_doe');
-- Error: Duplicate entry 'john@example.com' for key 'email'
UNIQUE vs PRIMARY KEY
While both enforce uniqueness, they serve different purposes. A PRIMARY KEY identifies each row uniquely and cannot contain NULL values. A UNIQUE constraint enforces uniqueness on alternate keys and allows NULL values (with database-specific behavior for multiple NULLs).
CREATE TABLE products (
product_id INT PRIMARY KEY, -- One per table, NOT NULL
sku VARCHAR(50) UNIQUE, -- Alternate key, allows NULL
barcode VARCHAR(50) UNIQUE, -- Another alternate key
name VARCHAR(255) NOT NULL
);
-- Valid: NULL values in UNIQUE columns
INSERT INTO products (product_id, sku, barcode, name)
VALUES (1, 'SKU-001', NULL, 'Product A');
INSERT INTO products (product_id, sku, barcode, name)
VALUES (2, 'SKU-002', NULL, 'Product B');
-- Succeeds in most databases (NULL != NULL)
-- Invalid: Duplicate non-NULL value
INSERT INTO products (product_id, sku, barcode, name)
VALUES (3, 'SKU-001', NULL, 'Product C');
-- Error: Duplicate entry 'SKU-001'
Creating UNIQUE Constraints
You can define UNIQUE constraints during table creation or add them to existing tables. Named constraints provide better error messages and easier management.
-- Inline constraint (unnamed)
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
ssn VARCHAR(11) UNIQUE,
email VARCHAR(255) UNIQUE
);
-- Named constraints (recommended)
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
ssn VARCHAR(11),
email VARCHAR(255),
CONSTRAINT uq_employee_ssn UNIQUE (ssn),
CONSTRAINT uq_employee_email UNIQUE (email)
);
-- Adding UNIQUE constraint to existing table
ALTER TABLE employees
ADD CONSTRAINT uq_employee_phone UNIQUE (phone_number);
-- Dropping UNIQUE constraint
ALTER TABLE employees
DROP CONSTRAINT uq_employee_phone;
Composite UNIQUE Constraints
Composite UNIQUE constraints enforce uniqueness across multiple columns. The combination of values must be unique, but individual column values can repeat.
CREATE TABLE course_enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
semester VARCHAR(20) NOT NULL,
CONSTRAINT uq_enrollment UNIQUE (student_id, course_id, semester)
);
-- Valid: Same student, different courses
INSERT INTO course_enrollments VALUES (1, 101, 201, '2024-Spring');
INSERT INTO course_enrollments VALUES (2, 101, 202, '2024-Spring');
-- Valid: Same course, different semesters
INSERT INTO course_enrollments VALUES (3, 101, 201, '2024-Fall');
-- Invalid: Duplicate combination
INSERT INTO course_enrollments VALUES (4, 101, 201, '2024-Spring');
-- Error: Duplicate entry '101-201-2024-Spring'
Handling NULL Values
NULL handling in UNIQUE constraints varies by database system. Most treat each NULL as distinct (allowing multiple NULLs), but SQL Server treats NULLs as equal by default.
-- PostgreSQL, MySQL: Multiple NULLs allowed
CREATE TABLE contacts (
contact_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
phone VARCHAR(20) UNIQUE
);
INSERT INTO contacts VALUES (1, 'user1@example.com', NULL);
INSERT INTO contacts VALUES (2, 'user2@example.com', NULL);
-- Both succeed: NULL != NULL
-- SQL Server: Filtered unique index for multiple NULLs
CREATE UNIQUE INDEX uq_contacts_phone
ON contacts(phone)
WHERE phone IS NOT NULL;
-- PostgreSQL: NULLS NOT DISTINCT (v15+)
CREATE TABLE contacts (
contact_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE NULLS NOT DISTINCT
);
-- Only one NULL allowed
UNIQUE Constraints with Expressions
Some databases support UNIQUE constraints on expressions or partial indexes, enabling advanced uniqueness rules.
-- PostgreSQL: Case-insensitive unique email
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(255)
);
CREATE UNIQUE INDEX uq_users_email_lower
ON users(LOWER(email));
-- These conflict despite different cases
INSERT INTO users VALUES (1, 'John@Example.com');
INSERT INTO users VALUES (2, 'john@example.com');
-- Error: Duplicate key violates unique constraint
-- Partial unique index: Active users only
CREATE UNIQUE INDEX uq_active_username
ON users(username)
WHERE status = 'active';
-- Same username allowed if one is inactive
INSERT INTO users (user_id, username, status)
VALUES (1, 'johndoe', 'active');
INSERT INTO users (user_id, username, status)
VALUES (2, 'johndoe', 'inactive');
-- Succeeds
Performance Considerations
UNIQUE constraints create indexes automatically, improving lookup performance but adding overhead to modifications.
-- Check existing indexes
SELECT
table_name,
index_name,
column_name,
non_unique
FROM information_schema.statistics
WHERE table_name = 'users';
-- UNIQUE constraint creates index automatically
CREATE TABLE sessions (
session_id VARCHAR(64) PRIMARY KEY,
user_id INT NOT NULL,
token VARCHAR(128) UNIQUE, -- Index created automatically
created_at TIMESTAMP
);
-- Explicit index on foreign key for joins
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
Benchmark the impact on write operations:
-- Without UNIQUE constraint
CREATE TABLE logs_no_unique (
id INT PRIMARY KEY AUTO_INCREMENT,
request_id VARCHAR(64),
timestamp TIMESTAMP
);
-- With UNIQUE constraint
CREATE TABLE logs_with_unique (
id INT PRIMARY KEY AUTO_INCREMENT,
request_id VARCHAR(64) UNIQUE,
timestamp TIMESTAMP
);
-- Bulk insert performance comparison
-- logs_no_unique: ~10,000 inserts/second
-- logs_with_unique: ~8,000 inserts/second (20% overhead)
Deferrable Constraints
PostgreSQL supports deferrable UNIQUE constraints, useful for temporary constraint violations during transactions.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100),
display_order INT,
CONSTRAINT uq_display_order UNIQUE (display_order)
DEFERRABLE INITIALLY DEFERRED
);
-- Swap display orders in transaction
BEGIN;
UPDATE departments SET display_order = 999 WHERE dept_id = 1;
UPDATE departments SET display_order = 1 WHERE dept_id = 2;
UPDATE departments SET display_order = 2 WHERE dept_id = 1;
COMMIT;
-- Constraint checked at commit, not during updates
Error Handling
Handle UNIQUE constraint violations gracefully in application code.
-- MySQL: INSERT IGNORE
INSERT IGNORE INTO users (user_id, email, username)
VALUES (1, 'existing@example.com', 'newuser');
-- Silently fails if constraint violated
-- MySQL: ON DUPLICATE KEY UPDATE
INSERT INTO users (user_id, email, username, login_count)
VALUES (1, 'user@example.com', 'johndoe', 1)
ON DUPLICATE KEY UPDATE
login_count = login_count + 1;
-- PostgreSQL: ON CONFLICT
INSERT INTO users (user_id, email, username)
VALUES (1, 'user@example.com', 'johndoe')
ON CONFLICT (email)
DO UPDATE SET username = EXCLUDED.username;
-- SQL Server: MERGE
MERGE INTO users AS target
USING (SELECT 1 AS user_id, 'user@example.com' AS email) AS source
ON target.email = source.email
WHEN MATCHED THEN
UPDATE SET login_count = target.login_count + 1
WHEN NOT MATCHED THEN
INSERT (user_id, email) VALUES (source.user_id, source.email);
Best Practices
Choose appropriate columns for UNIQUE constraints based on business requirements, not technical convenience. Email addresses and usernames are obvious candidates, but consider domain-specific identifiers like order numbers, invoice numbers, or external system IDs.
Name your constraints explicitly for better error messages and easier maintenance. Use prefixes like uq_ to identify constraint types quickly.
Consider case sensitivity and collation for string columns. A constraint on email might allow ‘User@Example.com’ and ‘user@example.com’ as distinct values unless you use expression-based constraints.
Monitor index size and query performance. Each UNIQUE constraint adds storage overhead and slows down writes. Evaluate whether the data integrity benefit justifies the cost for high-volume tables.
Use composite UNIQUE constraints sparingly. They’re powerful but can create large indexes and complex error conditions. Document the business rule clearly when using them.