Foreign keys across multipe mysql database instances-CodePudding

We are using mysql 8.0.30 We have two databases: PurchaseDB and ShopfloorDB with each having its own set of 100s of tables with several foreign key constraints between the tables. One example: PurchaseDB has a table purchases which is as follows:

 table purchases (
  mat  varchar(64)
);

ShopfloorDB has a table bom which is as follows:

 table bom (
  mat_one varchar(64)
  CONSTRAINT FK_bom_mat FOREIGN KEY (mat_one) REFERENCES PurchaseDB.purchases(mat)
);

The above works well because both 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 anymore. Is there a way to define these constraints in this configuration? It is not cost effective to manage these constraints via code.

CodePudding user response：

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.