List tables with the most foreign keys in MariaDB database

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

See also:

Query

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,
         table_name
order by foreign_keys desc,
         referenced_tables desc;

Columns

  • 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.

Rows

  • 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:

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