Unique index behavior in SQL

Introduction

A unique index helps keep data clean by making sure no two rows have the same value in certain columns.

When you want to make sure no two users have the same email address.

When you want to prevent duplicate product codes in a store database.

When you want to ensure each employee has a unique ID number.

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

When you want to avoid mistakes caused by duplicate entries.

Syntax

SQL

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

A unique index can be created on one or more columns.

It prevents duplicate values in the indexed columns.

Examples

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

SQL

CREATE UNIQUE INDEX idx_email ON users (email);

Ensures each product_code in the products table is unique.

SQL

CREATE UNIQUE INDEX idx_product_code ON products (product_code);

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

SQL

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

Sample Program

This example creates a table employees with a unique index on the email column. The third insert fails because it tries to add a duplicate email.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  email VARCHAR(100)
);

CREATE UNIQUE INDEX idx_email ON employees (email);

INSERT INTO employees (id, email) VALUES (1, '[e-mail]');
INSERT INTO employees (id, email) VALUES (2, '[e-mail]');
-- This next insert will fail because the email is already used
INSERT INTO employees (id, email) VALUES (3, '[e-mail]');

Important Notes

Unique indexes help keep your data accurate by stopping duplicates.

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

Unique indexes can also improve search speed on those columns.

Summary

Unique indexes prevent duplicate values in one or more columns.

They help keep data clean and consistent.

Inserting duplicates into unique indexed columns causes errors.