A SQL Unique Index is a constraint that ensures no two rows in the indexed columns of a table have the same values, thereby preventing duplicate entries. Unique indexes are crucial for maintaining data integrity, particularly when you need specific columns or combinations of columns to hold only unique values.
In this article, we will explore the concept of SQL Unique Indexes, their syntax, use cases, and practical examples with detailed explanations. By the end, you’ll have a clear understanding of how and when to use unique indexes to improve your database design and ensure data consistency.
What is a SQL Unique Index?
A SQL Unique Index is a database constraint that ensures the values in the indexed column(s) are unique across all rows in a table. It can be applied to one or more columns, ensuring data integrity by preventing duplicate values. Unique indexes can be created for both single columns and combinations of multiple columns. Additionally, they automatically exclude NULL
values if multiple rows contain NULL
in the indexed columns, further maintaining data consistency.
Key Characteristics of SQL Unique Index
- If a unique index is created on a single column, all rows in that column must be unique.
- If the unique index is created on multiple columns, the combination of values in these columns must be unique.
- A unique index cannot be created on a column if duplicate values already exist.
- Unique indexes improve query performance by optimizing data retrieval.
Syntax:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ..., columnN);
Key Terms:
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
Let’s create a sample CUSTOMERS
table containing customer details such as ID
, NAME
, AGE
, ADDRESS
, and SALARY
. This table serves as the basis for demonstrating the creation and use of unique indexes. Each row represents a unique customer, and we will apply unique constraints to specific columns to illustrate their functionality.
Query:
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(10, 4),
PRIMARY KEY(ID)
);
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000),
(2, 'Khilan', 25, 'Delhi', 1500),
(3, 'Kaushik', 23, 'Kota', 2000),
(4, 'Chaitali', 26, 'Mumbai', 6500),
(5, 'Hardik', 27, 'Bhopal', 8500),
(6, 'Komal', 22, 'Hyderabad', 9000),
(7, 'Muffy', 24, 'Indore', 5500);
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:
Query OK, 0 rows affected |
---|
Explanation:
The output confirms the successful creation of the unique index on the NAME
column. No rows are affected since the index is applied to existing data, and any future attempts to insert duplicate values will result in an error.
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:
Query OK, 0 rows affected
Explanation:
The unique index MUL_UNIQUE_INDEX
ensures that the combination of NAME
and AGE
values is unique for every row in the CUSTOMERS
table. If you attempt to insert or update a row with the same combination of NAME
and AGE
values as an existing row, the database engine will throw an error, ensuring no duplicate combinations are allowed.
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);
Output:
| ERROR 1062 (23000): Duplicate entry '2000.00' for key 'customers.UNIQUE_SALARY' |
Explanation:
The database engine prevents creating a unique index on the SALARY
column because it contains duplicate entries (2000.00
appears twice). To resolve this issue, we would need to remove or update duplicate values.
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:
Table | Non_unique | Key_name | Seq_in_index | Column_name |
---|
CUSTOMERS | 0 | PRIMARY | 1 | ID |
CUSTOMERS | 0 | UNIQUE_NAME | 1 | NAME |
CUSTOMERS | 0 | MUL_UNIQUE_INDEX | 1 | NAME |
CUSTOMERS | 0 | MUL_UNIQUE_INDEX | 2 | AGE |
Explanation:
The SHOW INDEX
command lists all indexes created on the table. It displays the index names, the associated columns, and their properties. This information helps verify the existence and structure of indexes applied to the table, ensuring their proper implementation.
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:
UPDATE CUSTOMERS SET ADDRESS = 'Mumbai' WHERE ADDRESS = 'Delhi';
Output:
| ERROR 1062 (23000): Duplicate entry 'Mumbai' for key 'customers.ADD_UNIQUE_INDEX' |
Explanation:
The UPDATE
query tries to change the value of ADDRESS
from 'Delhi' to 'Mumbai', but the column ADDRESS
has a unique index (ADD_UNIQUE_INDEX
). Since 'Mumbai' is already present in another row, the database engine prevents this operation to maintain the uniqueness constraint on the ADDRESS
column.
Benefits of SQL Unique Index
- Data Integrity: A unique index ensures that no duplicate values exist in the indexed column(s), maintaining the accuracy and reliability of your data.
- Improved Query Performance: Unique indexes optimize data retrieval for queries involving unique values, making searches faster and more efficient.
- Flexibility: Unique indexes can be applied to single columns or a combination of multiple columns, offering versatility in enforcing constraints across different data structures.
- Error Prevention: Unique indexes help prevent unintentional duplicates during
INSERT
or UPDATE
operations, safeguarding your database from inconsistent or invalid data
Conclusion
The SQL Unique Index is a powerful tool for maintaining data integrity and optimizing query performance. By enforcing uniqueness in one or more columns, it ensures that our database remains consistent and reliable. Use the CREATE UNIQUE INDEX
statement strategically to prevent duplicate entries and improve data retrieval efficiency. With examples and detailed explanations, this guide provides everything we need to understand and implement unique indexes effectively.
Similar Reads
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 ind
6 min read
PL/SQL Unique Index
A PL/SQL Unique Index is a powerful database object used to ensure the uniqueness of values in one or more columns of a table. In Oracle databases, indexes play an important role in enhancing query performance by enabling quick retrieval of rows. The unique index specifically enforces a rule that no
6 min read
PostgreSQL - UNIQUE Index
In PostgreSQL, a UNIQUE index is used to ensure that the values in one or more columns are unique across the rows in a table. This is essential for maintaining data integrity and avoiding duplicate entries. This article will provide a detailed overview of UNIQUE indexes, including syntax, examples,
4 min read
SQLite Indexes
SQLite is an embedded database that doesn't use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a quick,
8 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 quantitie
5 min read
SQL Indexes
When it comes to relational database performance optimizing, SQL indexes are a game changer. Imagine them like an index in a book instead of flipping through every page (row), the database can jump right to the data it requires. SQL Indexes are crucial elements in relational databases that greatly i
7 min read
Unique Indexes in MongoDB
A unique index in MongoDB is a powerful feature that ensures the uniqueness of values in specific fields within a collection by preventing duplicate entries. Understanding how to create a unique index in MongoDB is essential for maintaining data integrity especially for critical fields such as usern
7 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 kno
5 min read
MySQL Indexes
MySQL indexes are designed tools to increase the speed and efficiency of data retrieval operations within a database. Similar to a book index, which helps a user get to the information sought without having to go page after page, MySQL indexes let the database quickly find and retrieve your required
5 min read
Index in PL/SQL
PL/SQL, Oracle's extension to SQL, combines SQL with procedural programming features like loops, conditionals, and exception handling. It enables developers to create stored procedures, functions, triggers, and other database applications. As a block-structured language, PL/SQL allows seamless integ
5 min read