How can we check the indexes created by a UNIQUE constraint on a MySQL table?
SHOW INDEX statement is used to check the indexes created by a UNIQUE constraint on a MySQL table.
Syntax
SHOW INDEX from table_name;
Example
Suppose we have the table ‘empl’ which have a UNIQUE constraint on column ‘empno’.
mysql> describe empl;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno | int(11) | YES | UNI | NULL | |
| F_name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.23 sec)
Now as we know that UNIQUE constraint creates indexes which can be checked form the following query −
mysql> Show Index from empl\G
*************************** 1. row ***************************
Table: empl
Non_unique: 0
Key_name: empno
Seq_in_index: 1
Column_name: empno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.02 sec)
The ‘key_name’ in the above result set shows the name of the index and ‘column_name’ is the name of the column on which this index has been added.
Published on 16-Feb-2018 12:51:13
