SQL Unique Index

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 of SQL Unique Index

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

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 CUSTOMERS table. It ensures that every entry in the NAME column is distinct, enforcing data integrity.

Query:

CREATE UNIQUE INDEX UNIQUE_NAME ON CUSTOMERS(NAME);

Output:

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 CUSTOMERS(NAME, AGE);

Output:

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 CUSTOMERS(SALARY);

Error:

Example 4: Verifying Indexes in a Table

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

Query:

SHOW INDEX FROM CUSTOMERS;

Output:

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:

--Firstly, create a UNIQUE INDEX on ADDRESS colu
CREATE UNIQUE INDEX ADD_UNIQUE_INDEX ON CUSTOMERS(ADDRESS);

-- Attempts to assign a duplicate value to a column that has a UNIQUE index
UPDATE CUSTOMERS SET ADDRESS = 'London' WHERE ADDRESS = 'Sydney';

Error: