MySQL UNIQUE KEY

In this article, we describe how to use unique keys/unique indexes in MySQL to ensure that the values ​​of one or several columns are unique.

In MySQL, we can define many constraints on a table, such as primary key constraints, foreign key constraints. A unique key is also a commonly used constraint to ensure that the values ​​in one or more columns in a table are unique.

We use unique key constraints in many systems, such as:

  • There are login or email columns in the user table that are unique.
  • The Product ID column in the Product table is unique.
  • The order number column in the order table is unique.
  • In the daily statistical report, the three columns of year, month and day are used as the combined unique key.

Compared with the primary key, the primary key is used to represent the identification of a row. The primary key generally adopts a value that has nothing to do with the business, such as auto-increment, UUID, etc. The unique key is generally used to constrain the uniqueness of business-related data.

Primary key columns cannot contain NULL values, while unique key columns can contain NULL values.

In MySQL, KEY is a synonym for INDEX. A unique key corresponds to a unique index.

UNIQUE syntax

To define a unique key, please use the UNIQUE keyword. You can define a unique key when creating a table or add a unique key by modifying the table after creating the table.

Define a column as a unique key

Here is the syntax to define a column as a unique column when creating a table:

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

Define multiple columns as a unique key

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)
);

Here:

  • Multiple columns separated by commas are enclosed in parentheses after the keyword UNIQUE.
  • constraint_name is the name used to define a constraint. It is optional. If you do not define a constraint name, MySQL will automatically generate one.

Add unique key syntax

We can also add a unique key to an existing table. Here is the syntax for adding a unique key:

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] UNIQUE (column_list);

Note that the MySQL server will return an error if there is already duplicate data in a column to be set as a unique key of an existing table.

delete unique key syntax

To remove a unique key from a table, you can use the ALTER TABLE statement or the DELETE INDEX statement:

  • ALTER TABLE table_name DROP CONSTRAINT constraint_name
  • ALTER TABLE table_name DROP INDEX index_name
  • DROP INDEX index_name ON table_name

Unique key Examples

Let’s see some practical examples to understand the usage of the unique key constraint.

First, we first create a demo table user_hobby with a unique key with 2 columns:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `unique_user_hobby` UNIQUE(`user_id`, `hobby`)
);

Here, we define a constraint named unique_user_hobby unique, which contains 2 columns: user_id and hobby.

Then, we insert two rows for testing:

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, 'Football'), (1, 'Swimming');

Now let’s look at the data in the table:

SELECT * FROM user_hobby;

+----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |       1 | Football |
|        2 |       1 | Swimming |
+----------+---------+----------+
2 rows in set (0.00 sec)

Unique constraint

Let’s insert another row with the same data as the existing user_id and hobby columns

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, 'Football');

MySQL returned an error: ERROR 1062 (23000): Duplicate entry ‘1-Football’ for key ‘user_hobby.unique_user_hobby’.

Here the unique key constraint unique_user_hobby avoids inserting duplicate data.

Delete unique key

Let’s delete the unique key with the following statement:

DROP INDEX unique_user_hobby ON user_hobby;

You need to provide the constraint name for deletion. If you don’t know its name or you didn’t specify a constraint name when creating the unique key, use the SHOW INDEX statement display all the index names in a table:

SHOW INDEX FROM user_hobby;

+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_hobby |          0 | PRIMARY           |            1 | hobby_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_hobby |          0 | unique_user_hobby |            1 | user_id     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_hobby |          0 | unique_user_hobby |            2 | hobby       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

Note that the value in the key_name column is the constraint name.

Unique keys and NULL values

Unlike primary keys, unique keys allow columns within them to accept NULL values . However, the NULL value breaks the unique key constraint. That is, the unique key is invalid for the NULL value. Let’s look at the example below.

Now let’s modify the table creation statement just now, which allows hobby columns NULL:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45),
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `unique_user_hobby` UNIQUE(`user_id`, `hobby`)
);

Let’s insert two rows of the same data:

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, NULL), (1, NULL);

Then let’s look at the data in the table:

SELECT * FROM user_hobby;

+----------+---------+-------+
| hobby_id | user_id | hobby |
+----------+---------+-------+
|        1 |       1 | NULL  |
|        2 |       1 | NULL  |
+----------+---------+-------+
2 rows in set (0.00 sec)

We saw duplicate data in two columns for the unique key. NULL makes the unique key invalid.

Conclusion

In this article, we learned the usage of unique keys/indexes in MySQL. Here are the main points of this article:

  • Unique keys is used to ensure the uniqueness of values ​​in one or more columns in a table.
  • You can use the UNIQUE keyword to define a unique key.
  • Unique key columns can be NULL, but primary keys cannot NULL.