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