MySQL Unique Index

In MySQL, a unique index is a crucial tool for ensuring that values in a specific column or a group of columns within a table are unique. This feature is essential to maintain data integrity by preventing duplicate entries where uniqueness is required. In this guide, we’ll explore MySQL’s unique index, covering its syntax, usage, benefits, and important considerations.

Optimizing data integrity and performance is critical in the ever-changing world of database administration. The UNIQUE INDEX is a strong way of doing this. This article will take it through understanding, implementing, and using the UNIQUE INDEX functionality in MySQL databases.

UNIQUE INDEX

UNIQUE INDEX is a database constraint that assures the uniqueness of values in a defined column or collection of columns. It prevents duplicate data in a table, enhancing data correctness and integrity. This property is critical for ensuring the quality of relational databases.

The syntax for creating a UNIQUE INDEX involves the CREATE INDEX statement.

• Creating a UNIQUE INDEX on a Single Column:

CREATE UNIQUE INDEX index_unique_column ON tbl_name(col_name);

• Creating a UNIQUE INDEX on Multiple Columns:

CREATE UNIQUE INDEX index_unique_columns ON tbl_name(co1, col2, …);

Exmaples of MySQL Unique Index

We are going to discuss the following examples of Unique Index for better understanding of topics:

Example 1: Creating a Table with a Single-Column Unique Index

Let’s consider a scenario where you are designing a table to store information about customers, and each customer is identified by a unique customer ID. Here’s how you can create a table with a single-column unique index:

-- Creating the products table with a single-column unique index
CREATE TABLE products (
    product_Id INT PRIMARY KEY,
    product_name VARCHAR(50),
    UNIQUE (product_Id)
);

Now, let’s insert a few values into the products table:

-- Inserting values into the products table
INSERT INTO products (product_Id, product_name) VALUES
(101, 'Computer'),
(102, 'Smartphone'),
(103, 'Headphones');

Output:

Product_Table

Upon executing the above SQL statements, you should see the following output:

3 rows affected

Example 2: Creating a Table with a Multi-Column Unique Index

Consider a library database where each book is identified by a combination of its title and author. Here’s how you can create a table with a multi-column unique index:

-- Creating the books table with a multi-column unique index
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title_name VARCHAR(100),
    author_name  VARCHAR(50),
    genre VARCHAR(30),
    UNIQUE (title_name, author_name)
);

Now, let’s insert a few values into the products table:

-- Inserting values into the books table
INSERT INTO books (book_id, title_name, author_name, genre) VALUES
(101, 'East Of Eden', ' John Steinbeck', 'Classic'),
(102, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction'),
(103, 'Brave New World', 'Aldous Huxley', 'Dystopian');

Output:

Upon executing the above SQL statements, you should see the following output:

 3 rows affected

Example 3: Creating a Table with Duplicate Columns

Let’s consider a scenario where you are designing a table to store information about customers, and each customer is identified by a unique customer ID. Here’s how you can create a table with a single-column unique index:

-- Creating the products table with a single-column unique index 
CREATE TABLE products ( 
product_Id INT PRIMARY KEY,     
product_name VARCHAR(50),     
UNIQUE (product_Id) );

Now, let’s insert a few values into the products table:

-- Inserting values into the products table 
INSERT INTO products (product_Id, product_name)
 VALUES
(101, 'Computer'),
(102, 'Smartphone'), 
(103, 'Headphones'),
(101),'Laptop';

Output:

Output

Error Code: 1062. Duplicate entry '101' for key 'products.product_Id'

Differences Between Unique Index and Primary Key

Characteristic	Unique Index	Primary Key
Basics	Ensures uniqueness of values in specified column(s), allowing NULL values.	Uniquely identifies each record in a table and implies non-NULLability.
Nullable Values	Allows NULL values in indexed columns.	Does not allow NULL values in indexed column(s).
Number of Columns	Can be applied to one or more columns, enforcing uniqueness across combinations.	Typically applied to a single column but can join multiple columns (composite primary key).
Number of Constraints	Multiple unique indexes can be created on a table for different sets of columns.	Only one primary key constraint can be defined for a table.
Purpose	Ensures data integrity by preventing duplicate values. Used when uniqueness is necessary, but NULL values may exist.	Serves as a unique identifier for each record and establishes relationships between tables.
Clustered vs. Non-Clustered	Can be either clustered or non-clustered, depending on the database engine.	In many databases, implemented as a clustered index by default, physically ordering rows.

Unique Index and Performance

Performance Aspect	Unique Index
Data Retrieval Speed	Enhances data retrieval speed by facilitating quick lookup of unique values.
Insert Operations	Slightly slower on insert operations compared to tables without indexes, as uniqueness must be checked before insertion.
Update Operations	This may result in slightly slower update operations due to the uniqueness check.
Delete Operations	Similar to update operations, delete operations may be marginally slower due to uniqueness verification.
Join Operations	Can improve the speed of join operations, as indexed columns allow for more efficient matching.
Index Size	Generally , larger index size compared to non-unique indexes, as it stores unique values for each entry.
Query Optimization	Optimizes queries involving WHERE clauses on indexed columns, leading to faster execution.
Table Scans	Reduces the need for full table scans when searching for specific values.
Concurrency	May impact concurrency in write-heavy scenarios due to its uniqueness.
Disk Space Usage	Requires additional disk space for storing the index data, contributing to increased storage requirements.

Altering a Table with a Unique Index

• To add a Unique Index to an existing table a single column.

ALTER TABLE tbl_name
ADD UNIQUE INDEX index_unique_column (col_name);

• To add a Unique Index to an existing table with multiple columns.

ALTER TABLE tbl_name
ADD UNIQUE INDEX index_unique_columns (col1, col2, ...);

• Removing a Unique Index
  • To remove a Unique Index from a table, we can use the ALTER TABLE statement with the DROP INDEX clause, must Ensure that specify the name of the Unique Index that want to drop.

ALTER TABLE tbl_name
DROP INDEX index_unique_column;

• Modifying a Unique Index
  • If we need to modify an existing Unique Index, the common approach is to drop and recreate it. First, drop the index, and then recreate it with the desired changes.

ALTER TABLE tbl_name
DROP INDEX index_unique_column;

ALTER TABLE tbl_name
ADD UNIQUE INDEX index_unique_column (new_column_name);

• Altering Table Columns
  • If the Unique Index is on multiple columns, ensure that the new configuration aligns with the desired uniqueness constraints.

ALTER TABLE tbl_name
DROP INDEX index_unique_columns;

ALTER TABLE tbl_name
ADD UNIQUE INDEX index_unique_columns (new_column1, new_column2, ...);

Dropping a Unique Index

To remove a Unique Index from a table, we can use the DROP INDEX statement.

DROP INDEX index_unique_column
ON tbl_name;

Using a Unique Index in Queries

• SELECT query using a UNIQUE INDEX:

SELECT *
FROM tbl_name
WHERE column_name = 'the_value';

• INSERT query with a UNIQUE INDEX:

INSERT INTO tbl_name(column1, column2, ...)
VALUES ('value1', 'value2', ...);

• UPDATE query with a UNIQUE INDEX:

UPDATE tbl_name
SET column_name = 'new_value'
WHERE unique_column = 'the_value';

• DELETE query with a UNIQUE INDEX:

DELETE FROM tbl_name
WHERE column_name = 'the_value';

Conclusion

In this article, we have gone through the basic of MySQL Unique Index, and its importance in mysql database. This article also show how to use the Unique Index in SELECT, INSERT, UPDATE, and DELETE operations as well as in Altering the table. Also learn the basic difference between the Unique Index and Primary Key, as well as the Performance of the Unique Index.