MySQL Unique index

It helps to maintain data integrity 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.

This query creates a unique index in the table. It can also remove duplicate index columns from the table. You can create a new index using the "UNIQUE" keyword. You can also alter the table to create a new and unique index column.

Syntax

The unique index syntax shows below.

CREATE UNIQUE INDEX index name
ON table name (column1, column2, columnN);

The unique index syntax shows below. Create a new table with a unique key for a unique index column.

CREATE TABLE table name(
column1 data type PRIMARY KEY,
column2 data type NOT NULL,
column3 data type NOT NULL,
column data type NOT NULL,
UNIQUE KEY (column1, column2, columnN)
);

The unique index syntax with the existing table is shown below.

ALTER TABLE table-name
ADD CONSTRAINT [constraint name] UNIQUE KEY (column1, column2, columnN);

Examples of the unique index

1) Example: Create a unique index with a new table.

Execute the below query to create a table with the unique index column. Here, unique index assign to multiple column. You can also apply this index on either single column or multiple columns.

mysql> create table index_table(
-> roll_number INT NOT NULL PRIMARY KEY,
-> number INT NOT NULL,
-> mark INT NOT NULL,
-> name varchar(45),
-> UNIQUE INDEX (roll_number, number));

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows the unique index column of the table. This output displays index name and index type as per the storage index. The Non_unique column shows 0 values.

2) Example: Create unique index with existing table

Execute the below query to create a unique index column in an existing table. Here, the index assigns to a single column. You can apply this index on either a single column or multiple columns.

mysql> create unique index mark on index_table(mark);

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows a unique index of the table. This output displays index type as per storage index. Here mark column updates as a unique index.

3) Example: The unique index with constraint example.

Execute the below query to create a unique index column in an existing table. Here, the index assigns to a single column. The "alter table" statement is used to add an index to the column. The key is known as an index of the table.

mysql> ALTER TABLE index_table
ADD CONSTRAINT UNIQUE KEY (mark);

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows a unique index of the table. This output displays index type as per storage index. Here mark column updates with a unique index.

4) Example: The unique index with the database name.

Execute the below query to create a unique index column in an existing table. Here, the index assigns to a single column. The database refers to the table name to add a unique index. You apply this index on either a single column or multiple columns.

mysql> create unique index mark on tutorial.index_table(mark);

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows a unique index of the table. This output displays index type as per storage index. Here mark column updates with a unique index. This index refers tutorial database with the "index_table" table.