List foreign keys with columns in MariaDB database

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

Query

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,
         fks.table_name,
         kcu.ordinal_position;

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

Columns

  • 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

Rows

  • 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

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