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