MySQL Unique Index

Last Updated: 26 June, 2023

In MySQL, a unique index is used to enforce 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 combination of values in the indexed columns. A unique index can be defined on a single column or a group of columns.

To create a unique index in MySQL, we can use the UNIQUE keyword in the CREATE INDEX or ALTER TABLE statements.

Here's the syntax for creating a unique index on a single column:

 CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
  UNIQUE (column_name)
 );

Alternatively, we can create a unique index on multiple columns using the following syntax:

 CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
  UNIQUE (column_name1, column_name2, ...)
 );

Here's an example of creating a unique index on a single column:

 CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  username VARCHAR(50),
  ...
 );

In this example, the email column has a unique index, ensuring that each email address stored in the table is unique.

If we want to add a unique index to an existing table, we can use the ALTER TABLE statement with the ADD keyword:

 ALTER TABLE table_name
 ADD UNIQUE (column_name);

It's important to note that a unique index will allow NULL values, but it will consider two NULL values as distinct. Therefore, multiple rows with NULL values in the indexed column(s) can coexist in the table.

Using unique indexes can help enforce data integrity and improve query performance when searching for unique values in a table.