SQL Unique Index

Last Updated : 27 Aug, 2026

A SQL Unique Index enforces uniqueness by ensuring that no duplicate values exist in specified column(s), helping maintain data integrity. It is widely used to prevent duplicate entries and keep database data consistent and reliable. It improves query performance by speeding up data retrieval.

Syntax

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ..., columnN);
  • index_name: The name of the unique index.
  • table_name: The name of the table where the index will be created.
  • column1, column2, ..., columnN: The columns on which the unique index is being applied.

Examples

employees table is created to demonstrate how unique indexes enforce uniqueness on specific columns.

3

Example 1: Creating a Unique Index on a Single Column

This example creates a unique index on the NAME column to prevent duplicate names in the employees table. It ensures that every entry in the NAME column is distinct, enforcing data integrity.

Query:

CREATE UNIQUE INDEX unique_name
ON employees (name);

Output:

Screenshot-2025-11-24-102749

Example 2: Creating a Unique Index on Multiple Columns

This example demonstrates how to enforce uniqueness across a combination of two columns by creating a unique index on NAME and AGE.

Query:

CREATE UNIQUE INDEX mul_unique_index
ON employees (name, age);

Output:

Screenshot-2025-11-24-102749

Example 3: Attempting to Create a Unique Index on a Column with Duplicate Values

This example illustrates the behavior of the database when trying to create a unique index on a column that already contains duplicate values.

Query:

CREATE UNIQUE INDEX unique_salary
ON employees (salary);

Error:

2

Example 4: Verifying Indexes in a Table

This example demonstrates how to verify the unique indexes created on the employees table using the SHOW INDEX command.

Query:

SHOW INDEX FROM employees;

Output:

1

Example 5: Handling Duplicate Entries in Indexed Columns

This example demonstrates what happens when an UPDATE operation attempts to assign a duplicate value to a column with a unique index.

Query:

CREATE UNIQUE INDEX idx_employees_address_unique
ON employees (address);

UPDATE employees
SET address = 'London'
WHERE address = 'Sydney';

Error:

4
Comment

Explore