MySQL Unique Index

Indexing is finding an unordered list into an ordered list, which makes fetching records faster and more convenient. This is possible by creating an entry for each value in the index columns.

Without indexing, it can be very tough to search the whole table to find the relevant information. Similar to the book index, MySQL indexing goes one together with each other.

Unique Index enforces the uniqueness of the value in one or more columns. We can create more than one unique Index in a single table, which is impossible with primary key constraints.

Syntax

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

Let us create Unique constraints while table creation, shall we?

Example

Let us consider a table with names and emails, and we want emails to be unique.

CREATE TABLE rec(    
    ID int AUTO_INCREMENT PRIMARY KEY,     
    Name varchar(45),     
    Email varchar(45),
    UNIQUE KEY unique_email (Email)  
);  

We have created a table called 'rec' with email as a unique constraint.

Let us check the Index of the rec table.

SHOW INDEXES FROM rec;  

We can see our specified unique_email constraint.

Let's insert some data into it.

INSERT INTO rec(ID, Name, Email)    
VALUES (1, 'ash', 'ash@pb.com'),    
(2, 'tomdan', 'dan@pb.com')

Let us try to add duplicate email as its unique constraint here; let us check if it allows it.

INSERT INTO rec(ID, Name, Email)    
VALUES (3, 'bob', 'dan@pb.com');

We can see here that it does not allow duplicate email, and it should be meant to be unique.
So when the requirement comes that name should be unique tomorrow, it can be done too.

CREATE UNIQUE INDEX index_namee  
ON rec (Name);  

So in this way, we can create a unique Index while creation and after creation when needed.