MySQL Unique Index

The MySQL unique index is an index that enforces the uniqueness of values in one or more columns of a table. It ensures that no two rows in the table can have the same values in the indexed column(s). If you attempt to insert or update a row with duplicate values in the unique index, MySQL will raise an error, preventing the operation.

Here’s how you can create a unique index in MySQL:

1. Creating a Unique Index During Table Creation: You can create a unique index when defining the table using the UNIQUE constraint. For example:

   CREATE TABLE products (
       product_id INT PRIMARY KEY,
       product_name VARCHAR(255) UNIQUE,
       price DECIMAL(10, 2)
   );

In the above example, a unique index is created on the product_name column, ensuring that no two products can have the same name.

2. Creating a Unique Index After Table Creation: You can also add a unique index to an existing table using the CREATE UNIQUE INDEX statement. For example:

   CREATE UNIQUE INDEX idx_product_name ON products (product_name);

This statement creates a unique index named idx_product_name on the product_name column of the products table.

3. Creating a Unique Index with Multiple Columns: You can create a unique index on multiple columns to ensure the uniqueness of combinations of values. For example:

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE,
       UNIQUE KEY (customer_id, order_date)
   );

In this case, the unique index ensures that there are no duplicate combinations of customer_id and order_date in the orders table.

4. Adding a Unique Constraint to an Existing Column: You can add a unique constraint to an existing column using the ALTER TABLE statement. For example:

   ALTER TABLE products
   ADD CONSTRAINT product_name_unique UNIQUE (product_name);

This statement adds a unique constraint to the product_name column in the products table.

Unique indexes are essential for maintaining data integrity and enforcing data quality in your database. They are commonly used to ensure that critical columns contain distinct values, such as unique usernames, email addresses, or primary keys.