How to Maintain Foreign Key Constraint Across Different Databases?

Question

I know, I know, putting two related tables on different databases isn't exactly the best design practice. But for whatever's sake, suppose that I have to do it absolutely. And I have to break up two foreign-key-related tables that were previously located in a database into two databases, that are located on two different servers, but I still want to maintain the database(s) integrity. What is the best way to do this?

Edit: I am using MySQL and Symfony

Answer 1

I can't think of any way to do this with standard MySQL.

You could write a plugin for MySQL Proxy, that manages referential integrity between the parent and child tables on different servers:

• Intercept INSERT and UPDATE against child table. Query for matching row in parent table. Fail INSERT/UPDATE if no match found in parent table.

• Intercept DELETE against parent table. Query for dependent rows in child table. Fail DELETE if any dependent rows found in child table. If the constraint is intended to support cascading behavior, do that instead of failing.

• Intercept UPDATE against parent table. If the primary key value is changing as part of the update, query for dependent rows found in child table. Fail UPDATE if any dependent rows found in child table. If the constraint is intended to support cascading behavior, do that instead of failing.

Note that you'd have to keep information about the referential integrity constraints in your MySQL Proxy plugin (or write a custom config file for your plugin that records the relationships). You can't use conventional FOREIGN KEY syntax to declare such constraints across MySQL instances.

Answer 2

Have you considered Federated tables? These are basically links to tables which are hosted on a different databases on a different/same host.

You can create a federated table locally and use that to enforce referential integrity. However, I cannot overemphasize the fact that this approach is fraught with future gotchas and not at all recommended.