UNIQUE INDEX in MySQL
Overview
UNIQUE INDEX in MySQL is used to ensure that the values in one or multiple columns are unique across all rows (or records) in a table. It prevents any duplicate entry into the table. It can be useful in situations where uniqueness has to be ensured but it doesn't necessarily need to be a primary key.
Introduction
A UNIQUE INDEX in MySQL is a feature that ensures that the values in one or multiple columns are unique across all rows (or records) in a table. It prevents any duplicate entry into the table, even if it doesn't necessarily need to be a primary key.
In other words, when we create a UNIQUE INDEX on one or more columns in a table, MySQL guarantees that the values in those columns will be unique for every row in the table.
The UNIQUE INDEX can be created at the time of table creation or it can be added later using the ALTER TABLE statement. It can be declared on one or multiple columns, and it can also include null values in the indexed columns.
Syntax and Usage
To create a UNIQUE INDEX, we can make use of CREATE UNIQUE INDEX() where we can declare the columns to be specified as the UNIQUE INDEX inside the parentheses. Moreover, there can be multiple columns mentioned to have unique values.
Here, the ON clause indicates that the UNIQUE INDEX is being applied to a particular table with its columns mentioned inside the parentheses.
As the uniqueness is enforced on one or more column(s) in a table, it is ensured that these columns contain unique values only, but it can also be a NULL value though.
There are yet other alternate ways to declare a UNIQUE INDEX in MySQL. We'll have a look at them in the upcoming sections.
The use of UNIQUE INDEX in MySQL also improves efficiency by making data retrieval faster, even though it doesn't appear to be something different on the surface.
Let's have a look at this simple query to fetch all the rows where a column contains the specified value.
Now, won't this query work even without the use of UNIQUE INDEX? Yes, it would. But what makes it different is that MySQL creates a separate index object on these columns that facilitates the database to perform an index-based lookup that helps it retrieve the required data quickly concerning the search criteria that we mention in the WHERE clause.
The same can be done without the use of a UNIQUE INDEX. But in that case, MySQL has to scan the entire table to find out the matching rows and this can impede the performance. But yes, you need to declare the column as a UNIQUE INDEX to improve its efficiency.
Differences between Unique Index and Primary Key
The focus of both PRIMARY KEY as well as UNIQUE INDEX in MySQL is to make sure that a column must have a unique value in each of its rows.
But there are a few major differences between the two that should be highlighted. A PRIMARY KEY is a special unique index that can be assigned to only one column/field of a table. UNIQUE INDEX, on the other hand, isn't limited to having only one column in a table. It can be applied to multiple columns. PRIMARY KEY doesn't allow the insertion of any null value in a column. However, we can have null values in the case of a UNIQUE INDEX. Previously, the allowance of null values was considered a bug, but that's not the case here.
When to Use Unique Index vs. Primary Key
As already mentioned, there can only be one primary key but multiple unique indexes within a table.
- A primary key prohibits the insertion of NULL values but a unique index doesn't.
- Most importantly, a primary key is meant to enforce the referential integrity between the tables and this makes sure that each of the columns in a table can be uniquely identified. Contrarily, a unique index is used to ensure the uniqueness within a table.
Unique Index and Performance
Using a UNIQUE INDEX can have both a positive as well as negative impact on the performance of the database to fetch rows based on the search criteria. Indexing the columns surely increases the performance if the value has to be searched in a column that is declared as a UNIQUE INDEX.
However, having several columns declared as a UNIQUE INDEX, MySQL needs to check the uniqueness constraint for each row being inserted in the column. This can also hamper the efficiency of the database.
Altering a Table with Unique Index
Even after the creation of a table, we have a way to declare UNIQUE INDEX in MySQL. You can also call it altering a table with a unique index.
Output:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| gadgets | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | YES | NULL |
If you have created a table and wish to create a UNIQUE INDEX with some of its columns following the creation of the table, you can do so by using the CREATE UNIQUE INDEX statement.
Output:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| gadgets | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | YES | NULL | |||
| gadgets | 0 | unique_ind | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | unique_ind | 2 | description | A | 0 | NULL | NULL | YES | BTREE | YES | NULL |
Alternatively, you can also use the ALTER statement to create a unique index. In the case of the ALTER statement, if you don't mention the name for the UNIQUE INDEX, then MySQL creates a unique index for all the specified columns with their unique index name being the same as the name of the first column.
Output:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| gadgets | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | YES | NULL | |||
| gadgets | 0 | unique_ind | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | unique_ind | 2 | description | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | name | 2 | description | A | 0 | NULL | NULL | YES | BTREE | YES | NULL |
And the name for the unique index will be shown when you have already mentioned it.
Output:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| gadgets | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | YES | NULL | |||
| gadgets | 0 | unique_ind | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | unique_ind | 2 | description | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | name | 2 | description | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | ind_un | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL | ||
| gadgets | 0 | ind_un | 2 | description | A | 0 | NULL | NULL | YES | BTREE | YES | NULL |
Dropping a Unique Index
To drop a unique index, you can use the ALTER TABLE statement with the DROP INDEX keyword, followed by the name of the index.
This ALTER statement will simply remove the entire index from the specified table.
You cannot modify the existing UNIQUE INDEX by adding or removing columns as there's no direct statement to do the same. The alternate way is to first remove the UNIQUE INDEX and create a new one as per the requirement.
Consider the above-created table in this section for an instance. We created a UNIQUE INDEX unique_ind with the columns name and description. What if we only need the column name to be indicated as a UNIQUE INDEX?
Since we already have an index with the two columns in it, we'll remove the existing one and create a new index. You can also create a new index separately for the column name though. However, to make changes in the existing index, here's the way:
Output:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| gadgets | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | YES | NULL |
Output:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| gadgets | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | YES | NULL | |||
| gadgets | 0 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | YES | NULL |
Using Unique Index in Queries
We can recreate the example of creating a table gadgets but it will also be having a unique index with a combination of both the name and description. Therefore, if you need to insert a new record, make sure that both of its fields name and description have unique values, or else it will lead to an error.
We can some records in this table to see what happens when there's a duplicate entry.
Output:
| id | name | price | category | description |
|---|---|---|---|---|
| 1 | iPhone 13 | 999.99 | Smartphone | Latest iPhone from Apple |
| 2 | Samsung Galaxy S21 | 899.99 | Smartphone | Flagship Android phone from Samsung |
| 3 | Sony WH-1000XM4 | 349.99 | Headphones | Wireless noise-cancelling headphones from Sony |
| 4 | DJI Mavic Air 2 | 799.99 | Drone | Compact and powerful drone |
| 5 | Dell XPS 13 | 1199.99 | Laptops | 13.3-inch FHD display, Intel Core i7 processor |
Let's insert a new record for iPhone 13 where the name and description are the same as the one existing in the table.
Output:
Thus, the use of a UNIQUE INDEX guarantees that duplicate entries won't be allowed.
Conclusion
- MySQL uses the UNIQUE INDEX function to guarantee that the values in one or more columns are distinct across all rows (or records) in a table. It prevents the table from having any duplicate entries.
- Using a UNIQUE INDEX can be helpful in cases when uniqueness must be guaranteed, but it is not required to be a primary key.
- A PRIMARY KEY doesn't allow the insertion of any null value in a column. But, we can have null values in the case of a UNIQUE INDEX.
- Primary keys are used to enforce referential integrity between tables, while unique indexes are used to enforce uniqueness within a table.
- To create a UNIQUE INDEX, we can make use of CREATE UNIQUE INDEX() where we can declare the columns to be specified as the UNIQUE INDEX.
See Also
To get more ideas about the index, you can visit these pages as well.