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.

Karthikeya Boyini

Published on 16-Feb-2018 12:51:13

• Related Questions & Answers
• How can we drop UNIQUE constraint from a MySQL table?
• How can we apply UNIQUE constraint to the field of an existing MySQL table?
• What is MySQL UNIQUE constraint and how can we apply it to the field of a table?
• How can we create multicolumn UNIQUE indexes?
• How can we remove multicolumn UNIQUE indexes?
• How can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?
• How can we emulate CHECK CONSTRAINT by using triggers?
• How can we emulate CHECK CONSTRAINT by using views?
• Dropping Unique constraint from MySQL table?
• Adding unique constraint to ALTER TABLE in MySQL
• How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
• How do I add a check constraint to a table in MySQL?
• How can we apply a NOT NULL constraint to a column of an existing MySQL table?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?