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:

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:

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

Differences Between Unique Index and Primary Key

Unique Index and Performance

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.

abulhax

Follow

Improve

Article Tags :

• Geeks Premier League
• Databases
• MySQL
• Geeks Premier League 2023