如何维护跨不同数据库的外键约束？ [英] How to Maintain Foreign Key Constraint Across Different Databases?

问题描述

我知道，我知道，把两个相关的表放在不同的数据库上并不是最好的设计方法。但是无论如何，假设我有绝对的做法。而且我必须将以前位于数据库中的两个与外键相关的表分解成位于两个不同服务器上的两个数据库 ，但是我仍然希望维护数据库。诚信什么是最好的方法？

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 Proxy ，它管理不同服务器上的父表和子表之间的引用完整性：

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

• 截取 INSERT 和 UPDATE 对于子表。查询父表中的匹配行。失败 INSERT / 更新如果父表中没有匹配。

• 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 如果约束是为了支持级联行为，那么而不是失败。

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.

这篇关于如何维护跨不同数据库的外键约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！