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.