MariaDB Unique Constraint

Summary: in this tutorial, you will learn about MariaDB unique constraints and how to use the unique constraints to enforce the uniqueness of values in a column or a set of columns in a table.

Introduction to MariaDB unique constraint

Sometimes, you may want to ensure values in a column or a set of columns are unique, for example, country codes of the countries, email addresses of customers, and so on.

To enforce a column or a set of columns to store only distinct values, you use unique constraints.

A unique constraint is an integrity constraint that ensures values in a column or set of columns to be unique.

To define a unique constraint for a column, you use the unique keyword after in the column definition as follows:

create table table_name(
    ...,
    column_name datatype unique,
    ...
);
Code language: SQL (Structured Query Language) (sql)

When you insert or update values in a column that has a unique constraint, MariaDB checks and rejects the change if the values that are being inserted or updated exists in the table.

To define a unique constraint for multiple columns, you use the unique constraint as a table constraint:

create table table_name(
    ...
    column1 datatype,
    column2 datatype,
    ...,
    unique(column1,column2,...)
);
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify a list of comma-separated columns in parentheses after the unique keyword. MariaDB uses the combination of values in all columns column1, column2, … to evaluate the uniqueness.

When you define a unique constraint without using a constraint name, MariaDB will automatically assign a generated name to the constraint.

To explicitly specify a name for the constraint, you use the constraint clause as follows:

constraint constraint_name
unique(column_list)
Code language: SQL (Structured Query Language) (sql)

Adding unique constraints

To add a unique constraint to an existing table, you use the alter table add constraint statement:

alter table table_name
add constraint constraint_name 
unique (column_list);
Code language: SQL (Structured Query Language) (sql)

Removing unique constraints

To drop a unique constraint, you use can use the drop index or alter table statement:

drop index_name on table_name;
Code language: SQL (Structured Query Language) (sql)

or

alter table table_name
drop index index_name;
Code language: SQL (Structured Query Language) (sql)

MariaDB unique constraint examples

Let’s take some example of using unique constraints.

A) Using MariaDB unique constraint for a column

First, create a new table called locations:

create table locations(
    location_id int auto_increment,
    name varchar(255),
    phone varchar(15) not null unique,
    primary key(location_id)
);
Code language: SQL (Structured Query Language) (sql)

In this example, we defined a unique constraint for the phone column.

Second, insert a new row into the locations table:

insert into locations(name, phone)    
values('The White House','202-456-1111');
Code language: SQL (Structured Query Language) (sql)

Third, attempt to insert a new location whose phone number that already exists in the phone column:

insert into locations(name, phone)    
values('1600 Pennsylvania Avenue NW','202-456-1111');
Code language: SQL (Structured Query Language) (sql)

MariaDB issued an error:

SQL Error (1062): Duplicate entry '202-456-1111' for key 'phone'
Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB unique constraint for multiple columns

First, add two new columns to the locations table:

alter table locations
add latitude dec(9,6) not null,
add longitude dec(9,6) not null;
Code language: SQL (Structured Query Language) (sql)

Second, add a unique constraint that includes both latitude and longitude columns:

alter table locations
add constraint coordinate
unique(latitude,longitude);
Code language: SQL (Structured Query Language) (sql)

Third, insert a new location:

insert into locations(name, phone, latitude, longitude)
values('Eiffel Tower','+33 892 70 12 39',48.858093,2.294694);
Code language: SQL (Structured Query Language) (sql)

Fourth, insert another location with the same latitude but different longitude:

insert into locations(name, phone, latitude, longitude)
values('Unknown Lake','+33 892 70 30 30',48.858093,2.594694);
Code language: SQL (Structured Query Language) (sql)

It works because the combination of latitude and longitude is unique.

Finally, insert a new location with the same latitude and longitude:

insert into locations(name, phone, latitude, longitude)
values('The New Eiffel Tower','+33 892 70 12 39',48.858093,2.294694);
Code language: SQL (Structured Query Language) (sql)

MariaDB issued an error because the pair (48.858093,2.294694) already exists.

In this tutorial, you have learned how to use the MariaDB unique constraint to enforce the uniqueness of values in a column or set of columns from a table.