MySQL Unique Index

A UNIQUE index ensures that duplicate values are not allowed in the indexed column or combination of columns.

• Ensures indexed values are unique.
• Can be created on one or more columns.
• Useful for columns such as email addresses.

Syntax

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Example

Consider a customers table:

Create a UNIQUE index on the email column:

CREATE UNIQUE INDEX idx_email
ON customers (email);

Output:

Now, if we try to insert a duplicate email:

INSERT INTO customers
VALUES (4, 'David', 'john@example.com');

Output:

• The record is not inserted because the email value must be unique.

UNIQUE Index on Multiple Columns

A UNIQUE index can also be created on multiple columns.

Syntax

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2);

Example

CREATE UNIQUE INDEX idx_customer
ON customers (customer_name, email);

• Here, the combination of customer_name and email must be unique.