foreign keys List in MySQL database

The query below returns the foreign key constraints defined in the user databases (schemas).

select concat(fks.constraint_schema, '.', fks.table_name) as foreign_table,

       '->' as rel,

       concat(fks.unique_constraint_schema, '.', fks.referenced_table_name)

              as primary_table,

       fks.constraint_name,

       group_concat(kcu.column_name

            order by position_in_unique_constraint separator ', ') 

             as fk_columns

from information_schema.referential_constraints fks

join information_schema.key_column_usage kcu

     on fks.constraint_schema = kcu.table_schema

     and fks.table_name = kcu.table_name

     and fks.constraint_name = kcu.constraint_name

-- where fks.constraint_schema = 'Database Name'

group by fks.constraint_schema,

         fks.table_name,

         fks.unique_constraint_schema,

         fks.referenced_table_name,

         fks.constraint_name

order by fks.constraint_schema,

         fks.table_name;

Note: if you need the information for a specific database (schema), then uncomment the where clause and provide Database name.

Columns

foreign_table - foreign table name with its database (schema) name

rel - relationship symbol implicating direction

primary_table - primary (referenced) table name with its database (schema) name

fk_constraint_name - foreign key constraint name

fk_columns - foreign key columns separated by ','

Rows

One row: represents one foreign key. If the foreign key consists of multiple columns (composite key), it is still represented as one row

Scope of rows: all foreign keys in the database (schema)

Ordered by: foreign database name and table name