Unique Index - (MySQL Indexes)

In MySQL, an index is used to improve the performance of queries. A unique index is used to ensure that the values in a column or a group of columns are unique. In this tutorial, we'll discuss unique indexes in MySQL.

Syntax

The syntax for creating a unique index in MySQL is as follows:

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Example

Let's say we have a table called "users" with columns "id", "username", and "email". We want to ensure that usernames are unique, so we create a unique index:

CREATE UNIQUE INDEX idx_users_username ON users (username);

Now, if we try to insert a user with a duplicate username, we'll get an error:

INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

The output will be:

ERROR 1062 (23000): Duplicate entry 'john' for key 'idx_users_username'

Explanation

In the example above, we created a unique index on the "username" column of the "users" table. This ensures that all values in that column are unique. When we tried to insert a new user with a duplicate username, we received an error because the unique index constraint was violated.

Use

Unique indexes are useful for ensuring that data in a column or a group of columns are unique. They can be used to prevent duplicates in primary keys, emails, usernames, and other columns.

Important Points

• A unique index constraint can be added to a column or a group of columns.
• A unique index will prevent duplicate values from being inserted into the indexed columns.
• A unique index can improve query performance for select operations using the indexed columns.

Summary

In this tutorial, we discussed unique indexes in MySQL. Unique indexes are useful for ensuring that data in a column or a group of columns are unique. We covered the syntax, example, explanation, use, and important points of unique indexes in MySQL. With this knowledge, you can now use unique indexes in your MySQL database to ensure data integrity and improve query performance.