Description:
In normalized relational databases like Sakila, users often need to discover how two tables are related through multiple foreign key steps. For example, finding a path from the rental table to the country table requires understanding intermediate joins like rental → staff → store → address → city → country.
Currently, MySQL exposes only direct foreign key relationships via information_schema.KEY_COLUMN_USAGE. There is no built-in way to programmatically trace or visualize multi-hop foreign key paths across tables. This is especially painful for data analysts, engineers, and BI users who rely on such schema understanding to write accurate queries.
While such a task can be approximated with recursive CTEs or external tools, it would be far more efficient if MySQL supported native metadata traversal or embedded relationship graphs.
How to repeat:
1. Try to trace the foreign key path from rental to country using MySQL Workbench or information_schema.KEY_COLUMN_USAGE.
2. Observe that only direct relationships are shown.
3. Attempt to determine full multi-hop paths — this requires manual tracing or recursive queries which are not natively supported.
Suggested fix:
*Add native support for recursive FK path discovery, such as:
sql
SHOW JOIN_PATHS FROM rental TO country;
*Expose relationship graphs via a system view or metadata function.
*Embed visual relationship navigation in MySQL Workbench or MySQL Shell.
*Integrate schema-aware query planners that suggest optimal join paths based on relationships.
*Provide APIs for external tools to consume FK relationship graphs programmatically.
These features would greatly reduce schema discovery overhead, speed up query development, and make MySQL more accessible for modern analytics and data engineering use cases.
