如何维护跨数据库的外键约束?

2021-12-26 00:00:00 mysql symfony1 database-design

我知道，我知道，将两个相关的表放在不同的数据库上并不是最好的设计实践.但无论如何，假设我必须绝对地去做.而且我必须将以前位于数据库中的两个与外键相关的表分解为两个数据库，位于两个不同的服务器上，但我仍然想维护数据库正直.这样做的最佳方法是什么?

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?

我正在使用 MySQL 和 Symfony

推荐答案

我想不出任何方法可以用标准 MySQL 做到这一点.

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

您可以为 MySQL 代理 编写一个插件，用于管理父表和子表之间的参照完整性在不同的服务器上:

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

  • 拦截子表的INSERT 和UPDATE.查询父表中的匹配行.如果在父表中找不到匹配项，则失败 INSERT/UPDATE.

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

拦截父表的DELETE.查询子表中的相关行.如果在子表中找到任何相关行，则失败 DELETE.如果约束旨在支持级联行为，请执行此操作而不是失败.

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.

拦截父表的UPDATE.如果主键值在更新过程中发生变化，则查询在子表中找到的相关行.如果在子表中找到任何相关行，则失败 UPDATE.如果约束旨在支持级联行为，请执行此操作而不是失败.

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.

请注意，您必须在 MySQL 代理插件中保留有关参照完整性约束的信息(或为记录关系的插件编写自定义配置文件).您不能使用传统的 FOREIGN KEY 语法来跨 MySQL 实例声明此类约束.

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.

相关文章