Fork me on GitHub

Project Notes

Primary Key Audit

How to list primary key details in a MySQL database

Notes

Need to check that all primary keys are appropriately defined in a MySQL database? Querying the information schema ( tables, columns, and statistics ) makes it possible to get a

The following example filters for select tables and shows the primary key definition including data type:

$ mysql -uroot
mysql>
    select
        tab.table_schema,
        tab.table_name,
        sta.index_name as pk_name,
        sta.seq_in_index as column_id,
        sta.column_name,
        col.data_type
    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'
    inner join information_schema.columns as col
            on col.table_schema = tab.table_schema
            and col.table_name = tab.table_name
            and col.column_name = sta.column_name
    where tab.table_type = 'BASE TABLE'
        and tab.table_schema = 'mysql' -- optionally filter by schema
        and tab.table_name like 'time%' -- optionally filter by table name
    order by tab.table_name,
        column_id;
+--------------+---------------------------+---------+-----------+--------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME                | pk_name | column_id | COLUMN_NAME        | DATA_TYPE |
+--------------+---------------------------+---------+-----------+--------------------+-----------+
| mysql        | time_zone                 | PRIMARY |         1 | Time_zone_id       | int       |
| mysql        | time_zone_leap_second     | PRIMARY |         1 | Transition_time    | bigint    |
| mysql        | time_zone_name            | PRIMARY |         1 | Name               | char      |
| mysql        | time_zone_transition      | PRIMARY |         1 | Time_zone_id       | int       |
| mysql        | time_zone_transition      | PRIMARY |         2 | Transition_time    | bigint    |
| mysql        | time_zone_transition_type | PRIMARY |         1 | Time_zone_id       | int       |
| mysql        | time_zone_transition_type | PRIMARY |         2 | Transition_type_id | int       |
+--------------+---------------------------+---------+-----------+--------------------+-----------+
7 rows in set (0.01 sec)

Credits and References

About LCK#216 DatabaseMySQL
Project Source on GitHub Return to the Project Catalog

This page is a web-friendly rendering of my project notes shared in the LittleCodingKata GitHub repository.

LittleCodingKata is my collection of programming exercises, research and code toys broadly spanning things that relate to programming and software development (languages, frameworks and tools).

These range from the trivial to the complex and serious. Many are inspired by existing work and I'll note credits and references where applicable. The focus is quite scattered, as I variously work on things new and important in the moment, or go back to revisit things from the past.

This is primarily a personal collection for my own edification and learning, but anyone who stumbles by is welcome to borrow, steal or reference the work here. And if you spot errors or issues I'd really appreciate some feedback - create an issue, send me an email or even send a pull-request.