Find All Foreign Key References
In my experience, a well-Normalized database winds up with lots of Foreign Key relationships between the tables. They are a real benefit, bringing so much ease and power in preserving data integrity. Best of all, they pass responsibility for maintaining that integrity to the RDBMS itself, rather than burying it in application code.
Looking at one table, it is relatively easy to see its list of Foreign Keys outward, to other tables.
Occasionally, though, we need to know what other tables have a Foreign Key defined for a given table. It is rare enough that I usually forget some of the little details in between uses. Rather than needing to do a web search every time, this Tickler post will help to jog my memory.
MySQL
-- what FKs reference this data?
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'MySchema'
AND REFERENCED_TABLE_NAME = 'MyTable';
These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an internet search every time. By posting them here, they will be easier to find as needed.)