MySQL Unique Indexes

When we design databases and tables, the primary key in the table is usually an auto-incrementing numeric column. Primary key columns must be unique. Sometimes, other columns in our table besides the primary key also need to have unique values, such as the order number in the order table, email and mobile phone number in the user table, etc. We can define these columns as unique indexes to ensure that no duplicate values can appear in these columns.

A MySQL unique index is a special index that not only speeds up data retrieval from a table, but also prevents duplicate values from appearing in one or more specified columns.

MySQL allows you to use the CREATE UNIQUE INDEX statement to create a unique index.

MySQL unique index syntax

To create a unique index, use the CREATE UNIQUE INDEX statement like this:

CREATE UNIQUE INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

Explanation:

The UNIQUE keyword indicates that this index is a unique index.
index_name is the name of the index. There should not be two indexes with the same name in a table.
table_name is the name of the table.
column_list is the column name(s) in the table. Multiple column names are separated by commas.

In addition to using the above syntax, you can specify one or more columns as unique keys when creating a table in the following ways:

Define one column as a unique key

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

If the unique key contains multiple columns, use the following syntax:

CREATE TABLE table_name(
column_name1 column_definition,
column_name2 column_definition,
...,
[CONSTRAINT constraint_name]
    UNIQUE(column_name1,column_name2)
);

For more detailed information, jump to the MySQL Unique Keys page.

Note: In MySQL, unique key and unique index mean the same thing. Because the keywords INDEX and KEY have the same meaning.

MySQL unique index examples

The following example designs a solution for a user system in a SAAS system for an enterprise tenant. We will design two tables:

The tenant table: tenant. This table has three columns: primary key (id), tenant name (name), and tenant email (email).
The tenant user table: tenant_user. This table has four columns: primary key (id), tenant ID (tenant_id), user login (username), and user email ( email).

Among them, the tenant email column in the tenant table must have a unique value, and the user login name in the tenant user table must be unique within an tenant.

Note that this is just a simple design, and the actual user scheme is much more complicated.

Please follow the steps below to execute this example:

Connect to the MySQL server using the mysql client tool:
```
mysql -u root -p
```
Enter the password for the root account and press Enter:
```
Enter password: ********
```

Create a database named testdb and select this database

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

Create the tenant table using the following statement:

CREATE TABLE IF NOT EXISTS tenant (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

Create a unique index on the email column

CREATE UNIQUE INDEX idx_tenant_email ON tenant(email);

Let’s show all indexes in the table tenant using the SHOW INDEXES statement:

SHOW INDEXES FROM tenant;

+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tenant |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tenant |          0 | idx_tenant_email |            1 | email       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Create tenant user table

CREATE TABLE IF NOT EXISTS tenant_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    nickname VARCHAR(50) NOT NULL
);

Create a unique index on tenant_id and username columns of the tenant user table

CREATE UNIQUE INDEX idx_tenant_username
ON tenant_user(tenant_id, username);

Here, we create a composite unique index, which guarantees that the combined values of tenant_id and username columns are unique.

Let’s show all indexes in the tenant_user table using the SHOW INDEXES statement:

SHOW INDEXES FROM tenant_user;

+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tenant_user |          0 | PRIMARY             |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tenant_user |          0 | idx_tenant_username |            1 | tenant_id   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tenant_user |          0 | idx_tenant_username |            2 | username    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Validate unique indexes on the tenant table.

Let’s insert a row into the tenant table using the following statement:
```
INSERT INTO `tenant` (name, email)
VALUES ('A', '[email protected]'), ('B', '[email protected]');
```
Let’s try inserting another row with the same email:
```
INSERT INTO `tenant` (name, email)
VALUES ('A', '[email protected]');
```
MySQL will report an error: ERROR 1062 (23000): Duplicate entry ‘[email protected]’ for key ’tenant.idx_tenant_email’.
Verify the unique composite index in the tenant_user table.

Let’s add two users with usernames a and b for the A tenant:
```
INSERT INTO `tenant_user` (tenant_id, username, nickname)
VALUES (1, 'a', 'A user1'), (1, 'b', 'A user2');
```
Let’s add a user named a for the B tenant:
```
INSERT INTO `tenant_user` (tenant_id, username, nickname)
VALUES (2, 'a', 'B user1');
```
Here, although the username of this user already exists, it can be inserted normally because the value of the tenant_id column is different.

Let’s try again to add a user named a for the B tenant:
```
INSERT INTO `tenant_user` (tenant_id, username, nickname)
VALUES (2, 'a', 'B user2');
```
Here, MySQL returns an error: ERROR 1062 (23000): Duplicate entry ‘2-a’ for key ’tenant_user.idx_tenant_username’.

Conclusion

MySQL unique indexes are special indexes that not only speed up data retrieval from a table, but also prevents duplicate values from appearing in one or more specified columns.

MySQL unique index syntax

MySQL unique index examples

Conclusion

On this page