Open In App

MySQL Unique Index

Last Updated : 05 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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

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:

Books_Table

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:

Dublicate_error

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.



Previous Article
Next Article

Similar Reads

Create Unique Constraint with NULL Columns in MySQL
Unique constraint in MySQL ensures that each value in the column is unique. If a column contains a NULL value, it is also treated as a unique value, but if a column contains multiple NULL values it can not have a unique constraint. In this article we will look over how we can Create unique constraints with NULL columns in MySQL, using the syntax, m
3 min read
MySQL UNIQUE Constraint
MySQL UNIQUE constraint ensures that the values in a column or group of columns remain unique, preventing duplicate entries in a column and maintaining the integrity of the table. UNIQUE Constraint in MySQLA UNIQUE constraint in MySQL prevents two records from having identical values in a column. A UNIQUE constraint can contain null values as long
4 min read
JavaScript Program to Check if Kth Index Elements are Unique
In this article, We have given a String list and a Kth index in which we have to check that each item in a list at that particular index should be unique. If they all are unique then we will print true in the console else we will print false in the console. Example: Input: test_list = [“gfg”, “best”, “for”, “geeks”], K = 1 .Output: False Explanatio
3 min read
MariaDB Unique Index
MariaDB is a fast, scalable, open-source community-supported relational database management system that’s also an enhanced version of MySQL. Content management systems (CMS) are a key application of MariaDB. A CMS is a publication system through which web creators can push and manage large quantities of content on a website. In this article, we wil
5 min read
MySQL DROP INDEX Statetement
MySQL is open-source and user-friendly. It creates a database to store and manipulate the data. To perform various operations users make requests by typing specific statements. The server responds to the information from the user and Displays it on the user side. One Of the commands in MySQL is DROP INDEX which is used for deleting an index in the
5 min read
MySQL CREATE INDEX Statement
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. CREATE INDEX statement is used to retrieve data from the database more quickly. CREATE INDEX statement is used to create an Index that allows MySQL to quickly locate and retrieve relevant data by reducing the need for ful
3 min read
MySQL SHOW INDEX
MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995. MySQL is reputed for its sturdy and quick functioning attribut
6 min read
Queries to find Maximum index R with bitwise AND from index L at least K
Given array A[] of size N and array Q[][2] of size M representing queries of the type {L, K}. The task for this problem is to answer queries for each query to find the largest index R such that bitwise AND of all elements from L to R is at least K. If there is no such index print -1 Examples: Input: A[] = {15, 14, 17, 42, 34}, Q[][2] = {{1, 7}, {2,
16 min read
JavaScript Program to Create an Array of Unique Values From Multiple Arrays Using Set Object
We have given multiple arrays consisting of numerical values, and our task is to create an output array that consists of unique values from the multiple input arrays. We will use the Set object in JavaScript language. Example: Input: inputarray1: [1,2,3,4,5] inputarray2: [4,5,6,7,8] Output: outputArray: [1,2,3,4,5,6,7,8] Table of ContentUsing Set a
4 min read
Flutter - Get the Unique Device ID
Flutter is a UI toolkit developed and maintained by Google. It was released in May 2017 to the public. It is now one of the most popular cross-platform development frameworks among beginners and experienced application developers. It has a straightforward learning curve and uses the Dart programming language for developing the applications. In this
2 min read