We are using MySQL 8.0.30. We have two databases, PurchaseDB and ShopfloorDB, with each having its own set of hundreds of tables with several foreign key constraints between the tables.
In PurchaseDB:
table purchases (
mat varchar(64)
);
In ShopfloorDB:
table bom (
mat_one varchar(64)
CONSTRAINT FK_bom_mat FOREIGN KEY (mat_one) REFERENCES PurchaseDB.purchases(mat)
);
The above works well because the databases are colocated on the same DBEngine. But, because of performance reasons, we need to move the ShopfloorDB to a different VM instance. That means they are not colocated any more.
How do we define these constraints in this configuration?
It is not cost-effective to manage these constraints via code.
No, foreign keys can only reference a table on the same instance.
MySQL's FEDERATED tables are the only feature that allows a reference to a table on a remote MySQL instance. But federated tables don't support foreign keys.
You will either have to colocate tables on the same MySQL instance if you have a strict requirement to use a foreign key constraint.
The alternative is to implement referential integrity using client code. But this can't be atomic, and it's susceptible to code bugs.
Or lastly, don't enforce referential integrity, and trust that the client code simply doesn't do the wrong thing. This is, sadly, what most projects do.