Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-unique-index/ below:

MySQL Unique Index - GeeksforGeeks

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 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, ...);

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

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

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

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'

Ensures uniqueness of values in specified column(s), allowing NULL values.

Uniquely identifies each record in a table and implies non-NULLability.

Allows NULL values in indexed columns.

Does not allow NULL values in indexed column(s).

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).

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.

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.

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.

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.

• 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, ...);

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

DROP INDEX index_unique_column
ON tbl_name;

• 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';

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4