The queries below return foreign key constraints 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,
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 your 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
Sample results
You could also get this
Get this interactive HTML data dictionary in minutes with Dataedo.