MySQL UNIQUE INDEX

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 define 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 ‘NOT NULL’ condition, whereas UNIQUE INDEX can accommodate null values as well. Unique INDEX is not just a special index that helps to maintain data integrity and safety, but it also improves data retrieval performance.

Syntax:

UNIQUE INDEX is defined by the “CREATE UNIQUE INDEX ()” statement. Thus the statement will create a column that is mentioned within the () as a unique column, allowing no duplicates in it.

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 itself.

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

How UNIQUE INDEX works in MySQL?

So let us create a table with both PRIMARY KEY and UNIQUE INDEX. Let us consider a table for storing details of employees with 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 id as PRIMARY KEY and 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 if the unique columns will accept duplicate values or not.

Code:

INSERT INTO EMP_DETAILS VALUES ('1','Alex','Potter','Belgium','Manager','alex.potter123@gmail.com');

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 to a second row of this table.

Code:

INSERT INTO EMP_DETAILS VALUES ('2','Alex','Potter','Belgium','Manager','alex.potter123@gmail.com');

Output:

We get the below error message as 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','alex.potter123@gmail.com');

Output:

We get the below error message as result:

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

The UNIQUE INDEX defined can also be dropped if it seems to be unnecessary on a later point of 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 to table as below.

Code:

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

Output:

So we created the unique index:

Example #6

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

Code:

SHOW INDEX FROM student_details;

Output:

Example #7

Here let’s remove the unique index added by previous query by using DROPINDEX command.

Code:

ALTER TABLE student_details DROP INDEX ind;

Output:

Example #8

Here now let’s try to retrieve the indexes in the table student_details.

Code:

SHOW INDEX FROM student_details;

Output:

So the unique_indexes which were inserted to the table student_details are now removed, which in turn 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.

This is a guide to MySQL UNIQUE INDEX. Here we discuss the introduction to MySQL UNIQUE INDEX along with how UNIQUE INDEX works with query examples. You may also have a look at the following articles to learn more –

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