MySQL UNIQUE INDEX

Updated May 16, 2023

Introduction to MySQL UNIQUE INDEX

UNIQUE INDEX constraints are used in MySQL to maintain the uniqueness of values in the database. MySQL has the ‘PRIMARY KEY’ constraint to define unique columns in a table. But it can specify only one column per table. So, when multiple columns are expected to have distinct values throughout the data, UNIQUE INDEX is used. Also, the Primary key constraint by default holds the ‘NOT NULL’ condition, whereas UNIQUE INDEX can also accommodate null values. Unique INDEX is not just a special index that helps to maintain data integrity and safety, but it also improves data retrieval performance.

Syntax:

The “CREATE UNIQUE INDEX ()” statement defines UNIQUE INDEX()”. Thus the statement will create a column mentioned within the () as a unique column, allowing no duplicates.

The syntax can be as below:

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

The above is to define a unique index explicitly. But we can define the columns as a UNIQUE INDEX while creating a table.

CREATE TABLE table_name(
column1 data_type,
….
UNIQUEINDEX (index_column1, index_column2 ….)
);

How does UNIQUE INDEX work in MySQL?

So let us create a table with a PRIMARY KEY and a UNIQUE INDEX. Let us consider a table for storing details of employees with the table name EMP_DETAILS. The table will have employee id, first name, second name, country, position, and email id as column names. We will keep ‘id’ as the PRIMARY KEY and define position and email as UNIQUE INDEX.

Code:

CREATE TABLE EMP_DETAILS (
id int,
first_name varchar(50),
secnd_name varchar(50),
country varchar(50),
position varchar(50),
email varchar(90),
PRIMARY KEY (id),
UNIQUE INDEX ind (position, email));

Output:

The table has the id as PRIMARY KEY; hence, it is by default NOT NULL. The columns position and email are marked UNIQUE, but since both were not defined as NOT NULL in the query, they can hold null values but not duplicates.

Example #1

We will insert values into this table and check whether the unique columns will accept duplicate values.

Code:

INSERT INTO EMP_DETAILS VALUES ('1','Alex','Potter','Belgium','Manager','[email protected]');

Output:

So we have inserted values to one row of the table EMP_DETAILS. With our previous query, we have defined the field position and email as unique indexes. So these fields will not accept duplicate values.

Example #2

Let’s try inserting the same values into a second row of this table.

Code:

INSERT INTO EMP_DETAILS VALUES ('2','Alex','Potter','Belgium','Manager','[email protected]');

Output:

We get the below error message as a result:

The first UNIQUE INDEX defined was field position. That is why the error message was displaying about the field position.

Example #3

Here let us see for the email field as well.

Code:

INSERT INTO EMP_DETAILS VALUES ('2','Alex','Potter','Belgium','Accountant','[email protected]');

Output:

We get the below error message as a result:

When the unique constraint is created at the unique backend index is defined automatically.

The UNIQUE INDEX defined can also be dropped if it seems to be unnecessary at a later point in time.

Example #4

Here let us consider another table for adding or removal of UNIQUE INDEX.

Code:

CREATE TABLE student_details (
id int,
first_name varchar(50),
secnd_name varchar(50),
class varchar(50),
subject1 varchar(50),
email varchar(90));

Output:

So we have just created a table named student_details. We have not defined any primary_keys or Unique_Indexes.

Example #5

Here let’s define the UNIQUE INDEX in the table below.

Code:

CREATE UNIQUE INDEX ind ON student_details(id, email);

Output:

So we created a unique index:

Example #6

Here let’s see the index in the table student_details.

Code:

SHOW INDEX FROM student_details;

Output:

Example #7

Let’s remove the unique index added by the previous query by using the DROP INDEX command.

Code:

ALTER TABLE student_details DROP INDEX ind;

Output:

Example #8

Now, let’s try retrieving the indexes in the table student_details.

Code:

SHOW INDEX FROM student_details;

Output:

So the unique_indexes inserted to the table student_details are now removed, which confirms that all columns will accept any number of duplicate values. Thus we have checked in detail creating or defining a UNIQUE INDEX, how to insert values into the index, and how to remove the unique index from a table.

Conclusion

Here we have seen the UNIQUE INDEX. In MySQL, UNIQUE INDEX is used to define multiple non-duplicate columns at once. While PRIMARY KEY constraint also assures non-duplicate values in a column, only one PRIMARY KEY can be defined per table. So for scenarios where multiple columns are to be made distinct, UNIQUE INDEX is used.

We hope that this EDUCBA information on “MySQL UNIQUE INDEX” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Conditions in MySQL
2. Wildcards in MySQL
3. Unique Key in MySQL
4. MySQL Trigger