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 will understand the Unique Index in MariaDB along with its syntax, examples, and so on.

MariaDB Unique Index

A unique index in MariaDB means that the values in a specified column or a group of columns are unique all over the table.
Unlike a normal index, a unique index applies a constraint on the data, preventing the insertion of duplicate values(data).
This improves data integrity and allows for improved querying or say fast run query.
In MariaDB, a primary key is a UNIQUE index. If no primary key is explicitly defined for a table, a unique index is created on the columns designated as the primary key.
Simply we can say a unique index will ensure that any of two rows of the same column in a table can’t have the same value.
Let’s take some examples to understand it.

MariaDB Unique Index Statement Examples

To create a unique index, the UNIQUE keyword is used with the CREATE INDEX statement.

Syntax:

CREATE UNIQUE INDEX index_name ON tbl_name (column1, column2, ...);

Example 1: Create Single Column Unique Index in Exiting Table

Let’s suppose we have a employees table(already created) which consist of some columns line first_name, last_name, username, userid, and userpassword as Columns. Now we will create a Unique index for username column.

Query:

CREATE UNIQUE INDEX unique_username ON employees (username);

Output:

output of single column unique index

Explanation: In the above query, we have create a unique index for the column username as unique_username of employees table.

Example 2: Create Multiple Columns Unique Index in Exiting Table

Let’s create a unique index on multiple columns of employees table

Query:

CREATE UNIQUE INDEX index_name  ON employees (first_name, last_name);

Output:

output multiple columns unique index

Explanation: In the above Query, we have create a unique index on column first_name, last_name of employees table.

Example 3: Adding a Single Column Unique Index in New Table Creation

Let’s create a new table and along with creation we will define the unique index for a column.

Query:

CREATE TABLE students 
(
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    UNIQUE INDEX index_sid (student_id)
);

Output:

output creating new table with unique Index column

Explanation: In the above Query, we have create a table called students which consist of student_id, student_name as Columns. Here we have created a Unique Index for the column student_id as a index_sid.

Example 4: Altering an Existing Table to Add a Unique Index to a Column

Let’s suppose we have created a table called customers and while creating we have forget to create the Unique index for the column called email. So we can resolve this problem with the help of ALTER TABLE. Let’s see how can we do.

Query:

ALTER TABLE customers
ADD UNIQUE INDEX index_email (email);

Output:

output alter table to add index column

Explanation: In the above query, we have create a Unique Index for the column called email as a index_email with the help of ALTER TABLE Command.

MariaDB Unique Index and Null Values

Its Allows one NULL value per unique indexed column, but NULL is not considered equal to any other value. Or we can say that, the multiple rows of that column can have NULL values. It Supports a fast search for unique values in the indexed columns. However, other then non-NULL values, each rows should must be unique value.

Example:

# Creating a table with a unique index on the 'studentId' column
//creating a table 
CREATE TABLE students 
(
    studentId INT UNIQUE,
    studentName VARCHAR(50)
);
//inserting data into the table
# Inserting data with NULL values in 'studentId'
INSERT INTO students (studentId, studentName) VALUES
(1, ' Student1 '),
(2, ' Student2 '),
(NULL, ' Student3 '),
(NULL, ' Student4 ');

Explanation: In the example, the unique index on the studentId column allows multiple NULL values. The uniqueness constraint is only applied to non-NULL values.

Output:

output MariaDB Unique Index and Null Values

MariaDB Unique Index and Unique Constraint

Unique Index

A unique index in MariaDB is a database object that ensures the uniqueness of values in one or more columns. It allows the database to optimize queries for unique values and helps maintain data integrity.

Unique Constraint

A unique constraint is almost a unique index with a specific name. It provides a more clear and detailed way of ensuring uniqueness, making it easier to understand the purpose of the constraint.

Syntax:

ALTER TABLE tbl_name 
ADD CONSTRAINT constraint_name 
UNIQUE (column1, column2, ...);

Example:

ALTER TABLE employees 
ADD CONSTRAINT unique_email 
UNIQUE (empEmail);

Output:

Mariadb Unique Contraints

Explanation: In the above Query, We have add the Constraints which is UNIQUE to the empEmail Columns as a unique_email

Conclusion

In this article, we looked at the basics of MariaDB Unique Index, and its operations with syntax and Examples. It is necessary to remember that, while unique indexes and unique constraints are related, but they are not the same thing. And using the UNIQUE Index we can improve our querying. Now after reading the whole article we have a more knowledge about the Unique Index in MariaDB.

Article Tags :

Databases

Geeks Premier League

MariaDB

Geeks Premier League 2023