List foreign keys with columns in MySQL database

The query below returns the foreign key constraint columns 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,
       kcu.ordinal_position as no,
       kcu.column_name as fk_column_name,
       '=' as 'join',
       kcu.referenced_column_name as pk_column_name,
       fks.constraint_name as fk_constraint_name
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 kcu.table_schema not in('information_schema','sys',
                              'mysql', 'performance_schema')
--    and fks.constraint_schema = 'database name'
order by fks.constraint_schema,

Note: if you need the information for a specific database (schema), then uncomment the table_schema condition and provide your database name.


  • foreign_table - foreign table name with database (schema) name
  • rel - relationship symbol implicating direction
  • primary_table - primary (referenced) table name with database (schema) name
  • no - id of the column in the key. Single column keys always have 1, composite keys have 1, 2, ... n for each column of the key
  • fk_column_name - foreign table column
  • join - "=" symbol indicating the joining operation for a pair of columns
  • pk_column_name - primary (referenced) table column
  • fk_constraint_name - foreign key constraint name


  • One row: represents one foreign key column. If the foreign key is composed of several columns (composite key), each column appears separately
  • Scope of rows: all foreign keys in a database (schema) and its columns
  • Ordered by: foreign table database (schema) name, table name and column ordinal position in the key

Sample results

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.