List all primary keys (PKs) in MySQL database

The query below lists all primary keys constraints (PK) in a database (schema).

See also: tables with their primary keys.

Query

select tab.table_schema as database_name,
    sta.index_name as pk_name,
    group_concat(distinct sta.column_name order by sta.column_name) as 'columns',
    tab.table_name
from information_schema.tables as tab
inner join information_schema.statistics as sta
        on sta.table_schema = tab.table_schema
        and sta.table_name = tab.table_name
        and sta.index_name = 'primary'
where tab.table_schema = 'your database name'
    and tab.table_type = 'BASE TABLE'
group by table_name
order by table_name;

Columns

  • table_schema - PK database (schema) name
  • pk_name - PK constraint name
  • columns - list of PK columns separated by ','
  • table_name - PK table name

Rows

  • One row: represents one primary key (table) in a database (schema)
  • Scope of rows: all PK constraints in a database (schema)
  • Ordered by: table name

Sample results

Sample results for the Sakila database (schema)

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.