MySQL UNIQUE Indexes

A `UNIQUE` index in MySQL ensures that all values in a column are distinct from one another, effectively preventing duplicate entries. It is used to maintain data integrity by enforcing the uniqueness of specific fields in a database table.

Usage

The `UNIQUE` index is used when you need to ensure that no two rows have the same value in one or more columns. This index can be created at the time of table creation or added to an existing table.

CREATE TABLE table_name (
    column1 column_definition,
    column2 column_definition,
    UNIQUE (column_name)
);

In this syntax, `UNIQUE (column_name)` ensures that all values in `column_name` are unique across the table.

Examples

1. Creating a Table with a UNIQUE Index

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50),
    email VARCHAR(100),
    UNIQUE (email)
);

This example creates a `users` table where the `email` column must contain unique values, preventing duplicate email addresses.

2. Adding a UNIQUE Index to an Existing Table

ALTER TABLE products
ADD UNIQUE (product_code);

Here, a `UNIQUE` index is added to the `product_code` column in the `products` table, ensuring that each product has a distinct code.

3. Creating a Composite UNIQUE Index

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    UNIQUE (customer_id, product_id)
);

This example creates a composite `UNIQUE` index on the combination of `customer_id` and `product_id`, ensuring that each customer can order a specific product only once.

Tips and Best Practices

• Index Only Necessary Columns. Use `UNIQUE` indexes on columns that truly require uniqueness to avoid unnecessary performance overhead.
• Consider Composite Indexes. When multiple columns form a unique constraint together, use composite indexes to enforce this rule.
• Understand NULL Handling. Remember that `UNIQUE` indexes treat `NULL` values as distinct, allowing multiple `NULLs` unless specified otherwise. For example, inserting multiple rows with a `NULL` value in a `UNIQUE` indexed column is allowed, as each `NULL` is considered unique.
• Monitor Performance. Regularly assess the impact of `UNIQUE` indexes on insertion speed, as they can slow down data entry due to additional checks.
• Difference from PRIMARY KEY. Unlike a `PRIMARY KEY`, a `UNIQUE` index does not automatically imply `NOT NULL`, and a table can have multiple `UNIQUE` indexes but only one `PRIMARY KEY`.
• Error Handling. Attempting to insert a duplicate value into a column with a `UNIQUE` index will result in an error, such as "Duplicate entry 'value' for key 'index_name'".
• Bulk Data Import Considerations. Be cautious of potential conflicts during bulk data imports if duplicates exist, as this will cause errors and potentially halt the import process.

Additional Comparisons

• PRIMARY KEY vs. UNIQUE Index. Both `PRIMARY KEY` and `UNIQUE` indexes enforce uniqueness, but `PRIMARY KEY` also requires a `NOT NULL` constraint and serves as a unique identifier for the table.
• Non-Unique Indexes. Unlike `UNIQUE` indexes, non-unique indexes do not enforce any uniqueness constraints on the data, merely improving query performance.