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

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

Example

Suppose we have table "employee" with a UNIQUE constraint on the "empid" column. It can be checked by 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 and there is only one index, created on the "empid" column.

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 have added another 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 names of the indexes, i.e. the two indexes "empid" and "empid_2" created on the "empid" column.

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 have added another 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, the three indexes "empid" and "empid_2", "empid_3" created on the 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 a unique index on the column the number of times we add the UNIQUE constraint on that column.

The above is the detailed content of What happens if I add a UNIQUE constraint on the same column multiple times?. For more information, please follow other related articles on the PHP Chinese website!