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;
Oldest comments (0)