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

MySQLMySQLi Database

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.

raja
Published on 16-Feb-2018 13:47:50
Advertisements