MySQL Unique Indexes

Unique indexes are a powerful feature in MySQL that serve two crucial purposes: they enforce data uniqueness while also potentially improving query performance. If you're building applications that require data integrity (and most do), understanding unique indexes is essential.

What is a Unique Index?

A unique index ensures that the indexed columns do not store duplicate values. It acts as both a constraint and a performance optimization tool.

Unlike regular indexes which only speed up queries, unique indexes also enforce a business rule: the values in the indexed column(s) must be unique across all rows in the table.

Key characteristics:

• Prevents duplicate entries in the specified column(s)
• Can include single or multiple columns (composite unique indexes)
• Allows NULL values (though only one NULL in MySQL)
• Improves query performance when searching by the indexed column(s)
• Is automatically created when you define a UNIQUE constraint or PRIMARY KEY

Creating Unique Indexes

There are several ways to create unique indexes in MySQL:

Method 1: During Table Creation

sql

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT,
    email VARCHAR(100),
    phone VARCHAR(15),
    PRIMARY KEY (customer_id),
    UNIQUE INDEX idx_email (email),
    UNIQUE INDEX idx_phone (phone)
);

Method 2: Adding to an Existing Table

sql

ALTER TABLE customers
ADD UNIQUE INDEX idx_email (email);

Method 3: Using CREATE INDEX Statement

sql

CREATE UNIQUE INDEX idx_phone
ON customers (phone);

Composite Unique Indexes

Sometimes uniqueness needs to be enforced across a combination of columns:

sql

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    UNIQUE INDEX idx_order_product (order_id, product_id)
);

In this example, the combination of order_id and product_id must be unique, preventing the same product from being added to the same order multiple times.

Unique Indexes vs. Primary Keys

While both enforce uniqueness, there are important differences:

Feature	Primary Key	Unique Index
NULL values	Not allowed	Allowed (one per column)
Number per table	Only one	Multiple allowed
Auto-creates index	Yes	Yes
Implicit naming	Always PRIMARY	System-generated if not specified

Practical Example: User Registration System

Let's see unique indexes in action with a user registration system:

sql

-- Create a users table with unique constraints
CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(15),
    PRIMARY KEY (user_id),
    UNIQUE INDEX idx_username (username),
    UNIQUE INDEX idx_email (email),
    UNIQUE INDEX idx_phone (phone)
);

-- Insert a user successfully
INSERT INTO users (username, email, phone)
VALUES ('johndoe', '[email protected]', '555-1234');

-- This will succeed (different values)
INSERT INTO users (username, email, phone)
VALUES ('janedoe', '[email protected]', '555-5678');

-- This will fail (duplicate email)
INSERT INTO users (username, email, phone)
VALUES ('johnsmith', '[email protected]', '555-9012');

The last insertion will produce an error:

Error Code: 1062. Duplicate entry '[email protected]' for key 'users.idx_email'

Handling Duplicates: ON DUPLICATE KEY

MySQL provides a useful feature to handle potential duplicates:

sql

INSERT INTO users (username, email, phone)
VALUES ('johnsmith', '[email protected]', '555-9012')
ON DUPLICATE KEY UPDATE
    phone = VALUES(phone);

This will update the existing user's phone number instead of failing.

Partial Unique Indexes

In MySQL 8.0+, you can create unique indexes on a subset of rows using a WHERE clause:

sql

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100),
    status ENUM('active', 'inactive', 'deleted')
);

-- Only enforce uniqueness for active employees
CREATE UNIQUE INDEX idx_active_email
ON employees (email)
WHERE status = 'active';

NULL Values in Unique Indexes

Unique indexes treat NULL values specially - MySQL allows multiple NULL values in a unique index:

sql

CREATE TABLE contacts (
    contact_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    UNIQUE INDEX idx_email (email)
);

-- Both inserts will work despite the UNIQUE index
INSERT INTO contacts (name, email) VALUES ('John', NULL);
INSERT INTO contacts (name, email) VALUES ('Jane', NULL);

Performance Implications

Unique indexes have both benefits and costs:

Benefits:

• Improved query performance for searches on indexed columns
• Data integrity enforcement at the database level
• Can be used by the query optimizer for better execution plans

Costs:

• Additional storage space required
• Slower INSERT, UPDATE and DELETE operations
• Maintenance overhead during large data changes

Real-world Use Cases

1. User Authentication Systems

sql

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    UNIQUE INDEX idx_username (username),
    UNIQUE INDEX idx_email (email)
);

2. Product Catalog with SKUs

sql

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    category_id INT,
    UNIQUE INDEX idx_sku (sku),
    INDEX idx_category (category_id)
);

3. Inventory Tracking

sql

CREATE TABLE inventory_locations (
    item_id INT,
    warehouse_id INT,
    location_code VARCHAR(10),
    quantity INT NOT NULL DEFAULT 0,
    PRIMARY KEY (item_id, warehouse_id),
    UNIQUE INDEX idx_location (warehouse_id, location_code)
);

Best Practices for Unique Indexes

1. Name indexes meaningfully: Use prefixes like idx_ or uidx_ for clarity.

2. Be selective: Only create unique indexes when necessary for data integrity.

3. Consider column order: For composite indexes, put the most selective columns first.

4. Watch for NULL values: Remember NULL handling can affect uniqueness enforcement.

5. Monitor performance: Check if indexes are being used and not causing excessive overhead.

6. Be careful with long strings: Consider partial indexing for long text columns.

7. Use with foreign keys: Consider adding unique constraints on columns referenced by foreign keys.

Troubleshooting Unique Index Issues

Finding Duplicate Values Before Creating a Unique Index

sql

SELECT email, COUNT(email)
FROM customers
GROUP BY email
HAVING COUNT(email) > 1;

Analyzing Index Usage

sql

EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';

Checking Existing Indexes

sql

SHOW INDEXES FROM customers;

Summary

Unique indexes are a fundamental feature in MySQL that serve the dual purpose of enforcing data uniqueness and improving query performance. We've covered:

• Creating unique indexes using different methods
• Composite unique indexes for multi-column uniqueness
• Differences between primary keys and unique indexes
• Handling NULL values and duplicates
• Performance implications and best practices
• Real-world applications

By properly implementing unique indexes, you can ensure data integrity while also optimizing the performance of your MySQL database queries.

Additional Resources

• MySQL documentation on CREATE INDEX statements
• Books on MySQL optimization and indexing strategies
• Online courses on database design and MySQL performance tuning

Exercises

1. Create a books table with a unique ISBN column.
2. Design a table for a library system where a book can only be borrowed once by a member at a given time.
3. Write a query to find potential duplicate email addresses in an existing table.
4. Create a unique composite index on a student_courses table to ensure a student can't enroll in the same course twice.