How to Create Unique Index in MySQL: Syntax and Examples

To create a UNIQUE INDEX in MySQL, use the CREATE UNIQUE INDEX index_name ON table_name(column_name); statement. This ensures that all values in the indexed column are unique, preventing duplicate entries.

Syntax

The syntax to create a unique index in MySQL is:

• CREATE UNIQUE INDEX index_name ON table_name(column_name);
• index_name: The name you give to the unique index.
• table_name: The name of the table where the index is created.
• column_name: The column or columns to enforce uniqueness on.

sql

CREATE UNIQUE INDEX index_name ON table_name(column_name);

Example

This example creates a unique index on the email column of the users table to prevent duplicate email addresses.

sql

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE UNIQUE INDEX unique_email ON users(email);

Output

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when creating unique indexes include:

• Trying to create a unique index on columns that already have duplicate values, which causes an error.
• Not naming the index, which can make it harder to manage later.
• Confusing UNIQUE INDEX with PRIMARY KEY; a table can have multiple unique indexes but only one primary key.

sql

/* Wrong: duplicates exist, this will fail */
-- CREATE UNIQUE INDEX unique_email ON users(email);

/* Right: remove duplicates first or create on clean data */
CREATE UNIQUE INDEX unique_email ON users(email);

Quick Reference

Command	Description
CREATE UNIQUE INDEX index_name ON table_name(column_name);	Creates a unique index to enforce uniqueness on column(s).
DROP INDEX index_name ON table_name;	Removes an existing index from a table.
SHOW INDEX FROM table_name;	Lists all indexes on a table.

Key Takeaways

Use CREATE UNIQUE INDEX to enforce unique values in a column.

Ensure no duplicate data exists before creating a unique index.

Unique indexes can be created on one or multiple columns.

Unique indexes differ from primary keys; a table can have many unique indexes.

Name your indexes clearly for easier database management.