What happens if I will add a UNIQUE constraint on the same column for multiple times?

When we will add a UNIQUE constraint on the same column multiple times then MySQL will create the index on that column for a number of times we have added the UNIQUE constraint.

Example

Suppose we have the table ‘employee’ in which we have the UNIQUE constraint on ‘empid’ column. It can be checked form the following query −

mysql> Describe employee; +------------+-------------+------+-----+---------+-------+ | Field      | Type        | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | empid      | int(11)     | YES  | UNI | NULL    |       | | first_name | varchar(20) | YES  |     | NULL    |       | | last_name  | varchar(20) | YES  |     | NULL    |       | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.12 sec)

Now, when we run the query SHOW INDEX, it gives the name of the index, only one index, created on column ‘empid’.

mysql> Show index from employee\G; *************************** 1. row ***************************         Table: employee    Non_unique: 0      Key_name: empid  Seq_in_index: 1   Column_name: empid     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment: Index_comment: 1 row in set (0.00 sec)

With the help of the following query we added one more UNIQUE constraint on the same column ‘empid’ −

mysql> Alter table employee ADD UNIQUE(empid); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0

Now, when we run the query SHOW INDEX, it gives the name of the index, two indexes ‘empid’ and ‘empid_2’, created on column ‘empid’.

mysql> Show index from employee12\G; *************************** 1. row ***************************         Table: employee    Non_unique: 0      Key_name: empid  Seq_in_index: 1   Column_name: empid     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment: Index_comment: *************************** 2. row ***************************         Table: employee    Non_unique: 0      Key_name: empid_2  Seq_in_index: 1   Column_name: empid     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment: Index_comment: 2 rows in set (0.00 sec)

With the help of the following query we added one more UNIQUE constraint on the same column ‘empid’ −

mysql> Alter table employee ADD UNIQUE(empid); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0

Now, when we run the query SHOW INDEX, it gives the name of the index, three indexes ‘empid’ and ‘empid_2’, ‘empid_3’created on column ‘empid’.

mysql> Alter table employee ADD UNIQUE(empid); Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> Show index from employee\G; *************************** 1. row ***************************         Table: employee    Non_unique: 0      Key_name: empid  Seq_in_index: 1   Column_name: empid     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment: Index_comment: *************************** 2. row ***************************         Table: employee    Non_unique: 0      Key_name: empid_2  Seq_in_index: 1   Column_name: empid     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment: Index_comment: *************************** 3. row ***************************         Table: employee    Non_unique: 0      Key_name: empid_3  Seq_in_index: 1   Column_name: empid     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null: YES    Index_type: BTREE       Comment: Index_comment: 3 rows in set (0.00 sec)

In this sense, we can say that MySQL will create unique indexes on the column number of times we added the UNIQUE constraint on that column.