Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
478 Views
