一个事务中多个数据库的外键问题 [英] Foreign key problem with multiple db in one transaction

问题描述

我有一个数据库说 DB1，在 DB1 上的一个表 (DB1.t1) 中添加一列时，触发并在另一个 DB2 中以相同的名称 (DB2.t1) 创建表中同一行的副本.使用插入行的标识，我需要在 DB2 的另一个表 (DB2.t2) 中添加一些行.

I have one db say DB1, on adding one column in one table (DB1.t1) on DB1, triggers and create replica of same row in table by same name (DB2.t1) in another DB2. Using the identity of inserted row I need to add some rows in another table (DB2.t2) in DB2.

问题是，我需要在一个事务中插入 DB1.t1 和 DB2.t2.尝试这样做时，DB1.t1 插入正常，但由于未提交，DB2.t1 不包含该行，因此 DB2.t2 无法插入新行(缺少外键)，仍然在发送来自应用程序的数据时适当地.

Problem is, I need to insert in DB1.t1 and DB2.t2 in one transaction. When try to do that, DB1.t1 inserts fine, but since it is not committed, DB2.t1 does not contain the row, so DB2.t2 could not insert new rows (missing foreign key), still when data from application is sent properly.

一个选项是太commit DB1事务，然后做DB2操作，但是还有其他相关的功能.请建议实现它的最佳方法.

One option is too commit DB1 transaction, and then do DB2 operation, but there are other functionality related. Please suggest best way to achieve it.

推荐答案

好吧，Id 列不是有意义的键"，它们会让您到处乱跑.它们应该是无意义的物理标识符，并且您已经赋予它们意义.当您要求在另一个数据库中存在相同的 Ids 时.

Well, Id columns are not meaningful "keys", and they will trip you up all over the place. They are supposed to be meaningless, physical identifiers, and you have attached meaning to them. When you demand that the same Ids exist in another database.

您是否解决了 FK 违规问题?很可能您在 DB2 中的 DB1 行有一个完全不同的 Id；当然还有不同的父Ids.

Have you resolved the FK violation ? Most probably you have a totally different Id for the DB1 row, in DB2; and certainly different parent Ids.

您需要始终不指定Id值，并让服务器填写，或者始终指定Id 值，在两个数据库中.

You need to consistently not specify an Id value, and let the server fill it in, or always specify an Id value, in both databases.

第二个问题是您没有进行交易.多数据库事务完全没有问题.所以忘记 Id 列，它包含什么，并在两个 Dbs 中使用表的真实键.ids 会有所不同，但谁在乎(这意味着不需要为无意义的标识符附加意义).

The second problem is that you are not thniking transactionally. Multi-db transactions are no problem at all. So forget the Id column, what it contains, and use the real keys for the table, in both Dbs. The ids will be different, but who cares (that means release the need to attach meaning to the meaningless identifier).

这篇关于一个事务中多个数据库的外键问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！