MariaDB Quick-tip #8 - All foreign key constraints

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

All foreign key constraints

A well designed database relates its tables to each other using foreign key relations in order to ensure data integrity. But when the database has hundreds or even thousands of tables it can be a huge challenge finding all the tables related to a specific table you may have to modify.
The query below will find all the foreign key constraints between columns with a specific column name.

SET @DatabaseName := 'test_db'; SET @ColumnName := 'dept_no'; SELECT concat(rc.unique_constraint_schema, '.', rc.referenced_table_name) AS `Primary table`, concat(rc.constraint_schema, '.', rc.table_name) AS `Foreign table`, rc.constraint_name AS `Constraint name` FROM information_schema.referential_constraints rc JOIN information_schema.key_column_usage cu ON rc.constraint_schema = cu.table_schema AND rc.table_name = cu.table_name AND rc.constraint_name = cu.constraint_name WHERE rc.constraint_schema = @DatabaseName AND cu.COLUMN_NAME = @ColumnName ORDER BY rc.constraint_schema, rc.table_name;