List unique keys and indexes in MySQL database

The query below returns all primary keys and unique key constraints on tables in a MySQL database (schema).

Check out also list of unique indexes.

Query

select stat.table_schema as database_name,
       stat.table_name,
       stat.index_name,
       group_concat(stat.column_name
            order by stat.seq_in_index separator ', ') as columns,
       tco.constraint_type
from information_schema.statistics stat
join information_schema.table_constraints tco
     on stat.table_schema = tco.table_schema
     and stat.table_name = tco.table_name
     and stat.index_name = tco.constraint_name
where stat.non_unique = 0
      and stat.table_schema not in ('information_schema', 'sys',
                                    'performance_schema', 'mysql')
group by stat.table_schema,
         stat.table_name,
         stat.index_name,
         tco.constraint_type
order by stat.table_schema,
         stat.table_name;

Columns

  • database_name - database (schema) name
  • table_name - name of the table
  • index_name - name of the index
  • columns - index columns separated with ","
  • constraint_type
    • PRIMARY KEY - for primary keys
    • UNIQUE - for constraints that enforce uniqueness

Rows

  • One row: represents one index/key in the database (schema).
  • Scope of rows: all unique indexes and keys
  • Ordered by database (schema) and table name

Sample results

0
There are no comments. Click here to write the first comment.