MySQL UNIQUE INDEX
Indexing is a process to find an unordered list into an ordered list that allows us to retrieve records faster. It creates an entry for each value that appears in the index columns. It helps in maximizing the query’s efficiency while searching on tables in MySQL. Without indexing, we need to scan the whole table to find the relevant information. The working of MySQL indexing is similar to the book index.
Generally, we use the primary key constraint to enforce the uniqueness value of one or more columns. But, we can use only one primary key for each table. So if we want to make multiple sets of columns with unique values, the primary key constraint will not be used.
MySQL allows another constraint called the UNIQUE INDEX to enforce the uniqueness of values in one or more columns. We can create more than one UNIQUE index in a single table, which is not possible with the primary key constraint.
Syntax
The following is a generic syntax used to create a unique index in MySQL table:
MySQL allows another approach to enforcing the uniqueness value in one or more columns using the UNIQUE Key statement. We can read more information about the UNIQUE KEY here.
If we use a UNIQUE constraint in the table, MySQL automatically creates a UNIQUE index behind the scenes. The following statement explains how to create a unique constraint when we create a table.
NOTE: It is recommended to use the constraint name while creating a table. If we omit the constraint name, MySQL generates a name for this column automatically.
UNIQUE Index and NULL
NULL values in MySQL considers distinct values similar to other databases. Hence, we can store multiple NULL values in the UNIQUE index column. This feature of MySQL sometimes reported as a bug, but it is not a bug.
MySQL UNIQUE Index Examples
Let us understand it with the help of an example. Suppose we want to manage the employee details in a database application where we need email columns unique. Execute the following statement that creates a table “Employee_Detail” with a UNIQUE constraint:
If we execute the below statement, we can see that MySQL created a UNIQUE index for Email column of Employee_Detail table:
In the below screen, we can see that the Email column is created as a unique index.
Next, we are going to insert records to the table using the following statements:
The above statement executed successfully because all columns are unique. If we insert a record whose email is [email protected], we will get the duplicate error message.
The following output explains all of the above steps more clearly:
Suppose we want the Name and Phone of the Employee_Detail table is also unique. In this case, we will use the below statement to create a UNIQUE index for those columns:
If we execute the SHOW INDEX statement again, we can see that MySQL created a UNIQUE index index_name_phone for name and phone columns also.
Adding this record into the table produces an error. It is because of the combination of a name and phone already exists.
Look into this output:
