Find Foreign Keys Across Databases in MySql

Tags: MySQL, microservices

One of the pillars of microservice architectures is that the databases are not shared across different microservices.  In our implementation of microservices at Centerfield Media, we use a single MySQL instance with multiple schemas to support many of our microservices.  Each microservice has its own schema.  The temptation to link the schemas in some way such as a join inside a stored proc is great.  Discipline is required to avoid breaking this rule.  Another microservice rule buster is foreign keys across schemas.  I noticed we made this mistake recently.  

After this discovery I decided to audit our MySQL instance for any other illegal FKs.  Here's the query I used to do that:


SELECT i.CONSTRAINT_SCHEMA, 
	i. REFERENCED_TABLE_SCHEMA,
	i.table_name CONSTRAINT_TABLE,
	i.referenced_table_name REFERENCED_TABLE,
	i.constraint_name CONSTRAINT_NAME
FROM
	information_schema.KEY_COLUMN_USAGE i
WHERE
	i.referenced_table_name IS NOT NULL
	AND i.CONSTRAINT_SCHEMA <> i. REFERENCED_TABLE_SCHEMA
ORDER BY
	i.referenced_table_name;

This query returns all the foreign keys that reference a table in a different schema than the one its a part of. It will give you the schema with the FK and the schema it references as well as the tables in each and the name of the FK.

No Comments

Add a Comment