MySQL - Unique Index

MySQL - The Unique Index

Database indexes are special lookup tables that the database search engine can use to speed up data retrieval. While standard indexes are primarily for performance, a Unique Index serves a dual purpose: it enhances query performance and enforces data integrity by ensuring that all values in the indexed column (or combination of columns) are unique.

This constraint is fundamental for maintaining accurate and reliable data. For instance, you would use a unique index to ensure that no two users can register with the same email address or that every product has a distinct SKU.

Understanding Unique Indexes

A unique index can be created on one or more columns of a table. When in place, the database rejects any INSERT or UPDATE operation that would create a duplicate value in the indexed column(s).

Key Properties

• Uniqueness: For a single-column index, every value in that column must be unique. For a multi-column (composite) index, the combination of values across the specified columns must be unique.
• NULL Values: A unique index allows multiple NULL values. This is because NULL is treated as an unknown value, and two NULLs are not considered equal to each other. This differs from a PRIMARY KEY, which is implicitly unique and does not allow NULL values at all.
• Performance: Like any index, a unique index significantly speeds up queries that filter on the indexed column(s) (WHERE email = '...'). However, it adds a small overhead to data modification operations (INSERT, UPDATE) as the database must check for uniqueness.

Syntax

You can add a unique index when creating a table or add it to an existing table.

-- Method 1: Using CREATE UNIQUE INDEX on an existing table
CREATE UNIQUE INDEX index_name ON table_name (column1, column2...);

-- Method 2: Using ALTER TABLE (often preferred for clarity)
ALTER TABLE table_name ADD UNIQUE KEY index_name (column1, column2...);

-- Method 3: Defining it within CREATE TABLE
CREATE TABLE table_name (
    column1 DATATYPE,
    column2 DATATYPE,
    ...,
    UNIQUE KEY index_name (column1)
);

Practical Examples

Creating a Single-Column Unique Index

Let’s create a USERS table and ensure every EMAIL is unique.

CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(50) NOT NULL,
    EMAIL VARCHAR(100) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Now, add a unique index to the EMAIL column
ALTER TABLE USERS ADD UNIQUE KEY `uk_email` (EMAIL);

The uk_email name is a common naming convention for unique keys.

Attempting to Insert Duplicate Values

First, let’s insert a valid record.

INSERT INTO USERS (USERNAME, EMAIL) VALUES ('Alice', 'alice@example.com');
-- Query OK, 1 row affected

Now, let’s try to insert another user with the same email.

INSERT INTO USERS (USERNAME, EMAIL) VALUES ('Alice B.', 'alice@example.com');

MySQL will reject this operation and return an error, protecting your data integrity.

ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'users.uk_email'

Creating a Composite Unique Index

Imagine a system where a user can cast a vote on different items, but only once per item. We need to ensure the combination of USER_ID and ITEM_ID is unique.

CREATE TABLE VOTES (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USER_ID INT NOT NULL,
    ITEM_ID INT NOT NULL,
    VOTE_VALUE INT NOT NULL, -- e.g., 1 for upvote, -1 for downvote
    -- Composite unique index to prevent a user from voting twice on the same item
    UNIQUE KEY `uk_user_item` (USER_ID, ITEM_ID)
);

With this structure:

• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (1, 101, 1); — Succeeds
• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (2, 101, 1); — Succeeds (different user)
• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (1, 102, 1); — Succeeds (different item)
• INSERT INTO VOTES (USER_ID, ITEM_ID, VOTE_VALUE) VALUES (1, 101, -1); — Fails (duplicate USER_ID and ITEM_ID pair)

Managing and Viewing Indexes

You can list all indexes on a table to verify their existence and properties.

SHOW INDEX FROM USERS;

The output will be a table showing details for each index. Key columns to check are Key_name (the index’s name), Column_name (the indexed column), and Non_unique (which will be 0 for unique indexes and the primary key).

Creating a Unique Index Using a Client Program

Adding indexes is a common database migration task. These tasks should be scripted and version-controlled. Here’s how to do it in a modern Java application using JDBC.

Example: Java with try-with-resources

Java




import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UniqueIndexManager {

    // Use constants for connection details
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        // SQL to create the table
        String createTableSql = "CREATE TABLE IF NOT EXISTS EMPLOYEES (" +
                                " ID INT AUTO_INCREMENT PRIMARY KEY," +
                                " EMPLOYEE_ID VARCHAR(20) NOT NULL," +
                                " NAME VARCHAR(100)" +
                                ");";

        // SQL to add the unique index
        String addIndexSql = "ALTER TABLE EMPLOYEES ADD UNIQUE KEY uk_employee_id (EMPLOYEE_ID)";

        // The try-with-resources statement ensures that each resource is closed at the end of the statement
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            System.out.println("Successfully connected to the database...");

            // Create table if it doesn't exist
            stmt.execute(createTableSql);
            System.out.println("Table 'EMPLOYEES' is ready.");

            // Add the unique index
            stmt.executeUpdate(addIndexSql);
            System.out.println("Unique index 'uk_employee_id' created successfully on 'EMPLOYEES' table.");

        } catch (SQLException e) {
            // A more specific error check
            if (e.getSQLState().equals("42000") && e.getMessage().contains("Duplicate key name")) {
                System.out.println("Index already exists. No action taken.");
            } else {
                System.err.println("An SQL error occurred:");
                e.printStackTrace();
            }
        } catch (Exception e) {
            System.err.println("A general error occurred:");
            e.printStackTrace();
        }
    }
}


Output
The likely output on the first run:

Connected successfully...
Table 'EMPLOYEES' is ready.
Unique index 'uk_employee_id' created successfully on 'EMPLOYEES' table.

On a subsequent run:

Connected successfully...
Table 'EMPLOYEES' is ready.
Index already exists. No action taken.