在事务中插入父/子记录 [英] Inserting Parent/Child Records in A Transaction

问题描述

我有一个 C# 应用程序，它必须按层次顺序插入一个父记录和至少四个子记录.IOW，母合同适用于一个或多个地点，每个地点有一个或多个项目，每个项目有一个或多个服务，每个服务有一个或多个要求.该应用程序首先获取一组 Oracle 序列号，每个表序列中的每个记录都有一个序列号.无论出于何种原因(遗留数据库)，每条记录不仅有其父级的序列号，还有合同序列号.

I have a C# application that must insert one parent record, and at least four children records, in hierarchical order. IOW, a parent Contract applies to one or more locations, each location has one or more items, each item has one or more services, and each service has one or more requirements. The application first obtains a set of Oracle sequence numbers, one from each table sequence for each record. For whatever reason (legacy database) each record has not only its parent's sequence number, but also the contract sequence number.

因此，代码开始一个事务，插入带有父级序列号的父级，然后尝试插入位置记录——已经填充了作为 FK 的父级编号和它自己的表序列号.但是，我收到一个 Oracle-02291 错误，指出由于找不到父编号而违反了 FK.

So, the code begins a transaction, inserts the parent with the parent-level sequence number, then tries to insert the location record -- already populated with both the parent number as an FK, and its own table sequence number. However, I get an Oracle-02291 error that the FK is violated because the parent number can't be found.

INSERT into Contracts (contract_sequence_number, ...) values (10437, ...);
INSERT into Locations (location_sequence_number, contract_sequence_number, ...) 
    values (23733, 10437, ...);
...

我猜这是因为父级尚未提交，因此不可用.但是，如果 any 子记录失败，我无法提交父记录，因此在子插入之前提交.

I am guessing this is because the parent has not been committed, and therefore is not available. I can't commit the parent, however, if any of the child records fail, so a commit before the child insert is out.

我知道这是一个很常见的场景，答案一定是菜鸟前的.但是，到目前为止，我找到的所有答案都暗示在表中"找到父序列号以满足 FK.

I know that this is such a common scenario, the answer must be pre-noob. But, all the answers I've found, so far, imply that the parent sequence number is found "in the table" so as to satisfy the FK.

非常感谢您对我如何解决此问题的任何想法.

Any thoughts on how I fix this are greatly appreciated.

兰迪

推荐答案

感谢大家的投入.结果(正如我怀疑的那样)这是我的错.结果发现有两个非常相似的模式，我使用的连接可以访问这两个模式.两个模式都有同名的表.由于我不清楚的原因，父母正在插入一个模式，但孩子试图插入另一个模式.当然不能解决PK/FK的关系！

Thank you all for your input. Turned out (as I suspected) it was my bad. Turns out there are two very similar schemas, and the connection I was using has access to both. Both schemas have tables by the same name. For reasons not clear to me, the parent was inserting into one schmea, but the child was attempting to insert into the other schema. Of course it couldn't resolve the PK/FK relationship!

再次感谢.

这篇关于在事务中插入父/子记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！