List tables with the most foreign keys in MySQL database

The query below lists tables with their number of foreign keys and the number of tables to which they refer.

See also:


select concat(constraint_schema, '.', table_name) as 'table',
    count(*) as foreign_keys,
    count(distinct concat(unique_constraint_schema, '.', referenced_table_name))
            as referenced_tables
from information_schema.referential_constraints
-- where constraint_schema = 'database name' -- provide database name here
group by constraint_schema,
order by foreign_keys desc,
         referenced_tables desc;


  • table - name of the table
  • foreign_keys - number of foreign keys in a table
  • referenced_tables - number of referenced tables. Note that it is not the same as number of foreign keys, as multiple foreign keys may reference the same table.


  • One row: represents one table in a database (schema)
  • Scope of rows: tables in a database (schema) that have foreign keys (reference to other tables)
  • Ordered by: number of foreign keys and number of referenced tables in descending order

Sample results

Tables in the sakila database (schema) showing first those that have the most keys:

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