PostgreSQL Unique Index

Ensure data uniqueness with unique indexes

🔒 What is a Unique Index?

A unique index ensures no two rows have the same value in indexed columns. It speeds up queries while enforcing data integrity by preventing duplicate entries automatically. 


-- Create a unique index
CREATE UNIQUE INDEX idx_unique_email 
ON users(email);

Output:

CREATE INDEX

Key Unique Index Features

🛡️

Uniqueness

Prevents duplicate values 

-- No duplicate emails allowed
CREATE UNIQUE INDEX idx_email 
ON users(email);
✅

Integrity

Maintains data quality 

-- Unique usernames
CREATE UNIQUE INDEX idx_username 
ON accounts(username);
⚡

Performance

Fast lookups + uniqueness 

-- Speed + uniqueness
CREATE UNIQUE INDEX idx_sku 
ON products(sku);
🔗

Multi-Column

Unique combinations 

-- Unique user-product pair
CREATE UNIQUE INDEX idx_user_product 
ON favorites(user_id, product_id);

🔹 Creating a Unique Index

Unique indexes prevent duplicate values in a column. They're perfect for email addresses, usernames, or any field that must be unique across all rows. 

-- Create a users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    username VARCHAR(50)
);

-- Create unique index on email
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- This insert works
INSERT INTO users (email, username) VALUES ('[email protected]', 'john123');

-- This fails - duplicate email!
INSERT INTO users (email, username) VALUES ('[email protected]', 'john456');

Output:

INSERT 0 1
ERROR: duplicate key value violates unique constraint

🔹 Unique Index vs UNIQUE Constraint

Both enforce uniqueness, but constraints are more explicit and show up in table definitions. Unique indexes offer more flexibility with partial and expression indexes. 

-- Method 1: UNIQUE constraint (recommended for simple cases)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE,
    name VARCHAR(200)
);

-- Method 2: Unique index (more flexible)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    name VARCHAR(200)
);
CREATE UNIQUE INDEX idx_unique_sku ON products(sku);

Output:

Both methods prevent duplicate SKUs effectively

🔹 Multi-Column Unique Index

Create unique indexes on multiple columns to ensure combinations are unique. This is useful for preventing duplicate relationships like a user liking the same post twice. 

-- Create a favorites table
CREATE TABLE favorites (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    product_id INTEGER
);

-- Ensure each user can favorite a product only once
CREATE UNIQUE INDEX idx_unique_user_product 
ON favorites(user_id, product_id);

-- First favorite works
INSERT INTO favorites (user_id, product_id) VALUES (1, 100);

-- Second favorite for same combination fails
INSERT INTO favorites (user_id, product_id) VALUES (1, 100);

Output:

INSERT 0 1
ERROR: duplicate key value

🔹 Unique Index with NULL Values

By default, unique indexes allow multiple NULL values because NULL is not equal to NULL. Use NULLS NOT DISTINCT for stricter uniqueness including NULLs. 

-- Standard unique index (multiple NULLs allowed)
CREATE UNIQUE INDEX idx_phone ON users(phone);

-- These both work - NULL is not equal to NULL
INSERT INTO users (email, phone) VALUES ('[email protected]', NULL);
INSERT INTO users (email, phone) VALUES ('[email protected]', NULL);

-- PostgreSQL 15+: Only one NULL allowed
CREATE UNIQUE INDEX idx_phone_strict 
ON users(phone) NULLS NOT DISTINCT;

Output:

Standard index: Multiple NULLs allowed
NULLS NOT DISTINCT: Only one NULL allowed

🔹 Case-Insensitive Unique Index

Make unique indexes case-insensitive using LOWER() function. This prevents users from creating accounts with 'John' and 'john' as separate usernames. 

-- Create case-insensitive unique index
CREATE UNIQUE INDEX idx_username_lower 
ON users(LOWER(username));

-- This insert works
INSERT INTO users (username, email) 
VALUES ('JohnDoe', '[email protected]');

-- This fails - 'johndoe' matches 'JohnDoe' when lowercased
INSERT INTO users (username, email) 
VALUES ('johndoe', '[email protected]');

Output:

INSERT 0 1
ERROR: duplicate key value

🔹 Best Practices

Follow these guidelines to use unique indexes effectively and maintain data integrity while keeping your database performant and reliable.

✅ Do:

  • Use unique indexes for email addresses and usernames
  • Create unique indexes on natural keys (SKU, ISBN, etc.)
  • Use multi-column unique indexes for composite keys
  • Consider case-insensitive indexes for user input

❌ Don't:

  • Create unique indexes on columns with many duplicates
  • Forget to handle unique constraint errors in your application
  • Use unique indexes when you need soft deletes (use partial indexes)

🧠 Test Your Knowledge

What does a unique index prevent?