MariaDB Unique Index

Summary: in this tutorial, you will learn about MariaDB unique indexes and how to use them to enforce unique values in a column or a group of columns in a table.

Overview of MariaDB unique index

A unique index ensures that the index key columns don’t have duplicate values.

A unique index may consist of a column or a group of columns.

When a unique index has one column, the values in this column are unique.

If a unique index consists of multiple columns, the combination of values in these columns are unique.

Once a unique index is in place, you cannot insert or update values that cause a duplicate to the indexed columns.

To create a unique index, you use the create unique index statement as follows:

create unique index index_name
on table_name(indexed_column);
</code>Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the unique index after the create unique index keywords.
• Second, specify the name of the table to which the index belongs and a list of comma-separated indexed columns.

When you use the show indexes statement to display indexes of a table, the unique indexes will have values of zero (0) in the non_unique column.

show indexes from table_name;
</code>Code language: SQL (Structured Query Language) (sql)

MariaDB unique index examples

Let’s take some examples of using unique indexes.

A) Creating a unique index for one column example

See the following regions table from the sample database.

This query finds the region whose name is 'Polynesia':

select * 
from regions
where name = 'Polynesia';
</code>Code language: SQL (Structured Query Language) (sql)

To return the result set, the query optimizer has to scan the whole regions table:

explain
select * 
from regions
where name = 'Polynesia';
</code>Code language: SQL (Structured Query Language) (sql)

Suppose that the region names are distinct, you can create a unique index on the name column:

create unique index region_name
on regions(name);
</code>Code language: SQL (Structured Query Language) (sql)

To show all indexes from the regions table, you use the show indexes statement:

show indexes from regions;
</code>Code language: SQL (Structured Query Language) (sql)

The table regions has two unique indexes: one is the primary key index and the other is region_name that has just been created.

Now, if you search for regions by names, the query optimizer will leverage the unique index:

explain
select * 
from regions
where name = 'Polynesia';
</code>Code language: SQL (Structured Query Language) (sql)

In addition, you cannot insert a region with a name that already exists:

insert into 
	regions(name, continent_id)
values
	('Polynesia',1);</code>Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (1062): Duplicate entry 'Polynesia' for key 'region_name'

B) Creating a unique index for multiple columns example

First, create a table called ui_demos that has two columns for the demonstration:

create table ui_demos (
    c1 INT, 
    c2 INT
);
</code>Code language: SQL (Structured Query Language) (sql)

Next, create a unique index that includes both columns c1 and c2:

create unique index c12 
on ui_demos(c1,c2);
</code>Code language: SQL (Structured Query Language) (sql)

Then, insert a new row into the ui_demos table:

insert into ui_demos(c1,c2) 
values(1,1);
</code>Code language: SQL (Structured Query Language) (sql)

After that, insert another row into the t1 table.

insert into ui_demos(c1,c2) 
values(1,2);
</code>Code language: SQL (Structured Query Language) (sql)

Notice that the value 1 is repeated in the c1 column. However, the combination of values in both columns c1 and c2 is not duplicate.

Finally, insert another row into the ui_demos table:

insert into ui_demos(c1,c2)
values(1,2);
</code>Code language: SQL (Structured Query Language) (sql)

MariaDB issues an error::

SQL Error (1062): Duplicate entry '1-2' for key 'c12'  
</code>Code language: SQL (Structured Query Language) (sql)

MariaDB unique index and null values

In the database world, null values are special because they are markers that indicate the missing information. Therefore, a null value doesn’t equal to any value, even itself.

MariaDB respects the distinctions of null values when it comes to the unique index. Simply put, you can have multiple null values in a column that has a unique index.

Consider the following example.

First, create a new table that has one column:

create table ui_nulls(c int);
</code>Code language: SQL (Structured Query Language) (sql)

Second, create a unique index on the c column:

create unique index uic
on ui_nulls(c);
</code>Code language: SQL (Structured Query Language) (sql)

Third, insert a null value into the c column:

insert into ui_nulls(c)
values(null);
</code>Code language: SQL (Structured Query Language) (sql)

Finally, insert another null value into the c column:

insert into ui_nulls(c)
values(null);
</code>Code language: SQL (Structured Query Language) (sql)

It works as expected.

MariaDB unique index and unique constraint

When you create a unique constraint on a column of a group of columns, MariaDB automatically creates a unique index to enforce the constraint.

A unique constraint makes the purpose of a unique index more clear.

In this tutorial, you learned about the MariaDB unique index and how to create a unique index for a column or a group column in a table.