MySQL – Unique Index

In indexing, we convert an unordered list into an ordered list so that we can retrieve records more quickly. It creates an entry for each value contained in the index columns. In MySQL, it maximizes the query’s efficiency when searching on tables. In the absence of indexing, we must scan the entire table in order to locate the relevant information. MySQL indexing works in a similar manner to book indexing.

A primary key constraint is generally used to enforce the uniqueness of a column or columns. However, each table can only have one primary key. We will not use the primary key constraint if we wish to create multiple sets of columns with unique values.

The UNIQUE INDEX constraint allows MySQL to enforce uniqueness in one or more columns. There can be more than one UNIQUE index in a single table, which is not possible with a primary key constraint.

Syntax

Here is a generic syntax for creating a unique index in MySQL:

CREATE UNIQUE INDEX index_name  
ON table_name (index_column1, index_column2,...);  

It is also possible to enforce the uniqueness value in one or more columns by using the UNIQUE Key statement in MySQL. UNIQUE KEY information can be found here.

MySQL automatically creates a UNIQUE index behind the scenes when we use a UNIQUE constraint in the table. Creating a unique constraint for a table is explained in the following statement.

CREATE TABLE table_name(    
  col1 col_definition,    
  col2 col_definition,    
  ...    
  [CONSTRAINT constraint_name]    
  UNIQUE Key (column_name(s))    
);  

UNIQUE Index and NULL

Similar to other databases, MySQL considers NULL values to be distinct values. As a result, multiple NULL values can be stored in the UNIQUE index column. There are times when this feature of MySQL is reported as a bug, but it is not a bug.