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.