Unique indexes in MySQL

Introduction

Unique indexes help keep data clean by making sure no two rows have the same value in certain columns.

When you want to make sure email addresses in a user table are not repeated.

When you need to prevent duplicate product codes in an inventory list.

When you want to ensure each username in a system is unique.

When you want to speed up searches on columns that must have unique values.

Syntax

MySQL

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

Unique indexes can be created on one or more columns.

They prevent duplicate values in the indexed columns.

Examples

Creates a unique index on the email column in the users table to prevent duplicate emails.

MySQL

CREATE UNIQUE INDEX idx_email ON users (email);

Ensures each product_code in the products table is unique.

MySQL

CREATE UNIQUE INDEX idx_product_code ON products (product_code);

Prevents duplicate combinations of first and last names in the users table.

MySQL

CREATE UNIQUE INDEX idx_user_fullname ON users (first_name, last_name);

Sample Program

This example creates a users table, adds a unique index on the email column, inserts two users with different emails, then tries to insert a third user with an email that already exists. The last insert will fail due to the unique index.

MySQL

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

CREATE UNIQUE INDEX unique_email ON users (email);

INSERT INTO users (username, email) VALUES ('alice', '[e-mail]');
INSERT INTO users (username, email) VALUES ('bob', '[e-mail]');
-- This next insert will fail because the email is already used
INSERT INTO users (username, email) VALUES ('charlie', '[e-mail]');

Important Notes

Unique indexes improve data integrity by preventing duplicates.

Trying to insert a duplicate value in a unique index column causes an error.

You can create unique indexes when creating a table using UNIQUE constraints.

Summary

Unique indexes stop duplicate values in specified columns.

They help keep your data clean and reliable.

They can be created on one or more columns.