在事务中插入父/子记录
我有一个 C# 应用程序，必须按分层顺序插入一条父记录和至少四条子记录。 IOW，父合同适用于一个或多个位置，每个位置有一个或多个项目，每个项目有一个或多个服务，每个服务有一个或多个需求。应用程序首先获取一组 Oracle 序列号，每个记录的每个表序列都有一个序列号。无论出于何种原因（遗留数据库），每个记录不仅具有其父记录的序列号，而且还具有合同序列号。
因此，代码开始一个事务，插入具有父级序列号的父级，然后尝试插入位置记录——已经填充了作为 FK 的父级编号及其自己的表序列号。但是，我收到 Oracle-02291 错误，指出由于找不到父编号，因此 FK 被违反。
INSERT into Contracts (contract_sequence_number, ...) values (10437, ...);
INSERT into Locations (location_sequence_number, contract_sequence_number, ...)
values (23733, 10437, ...);
...
我猜测这是因为父级尚未提交，因此不可用。但是，如果任何子记录失败，我就无法提交父记录，因此子插入之前的提交就会失效。
我知道这是一个很常见的场景，答案一定是菜鸟前的。但是，到目前为止，我找到的所有答案都意味着父序列号是在“表中”找到的，以满足 FK。
非常感谢任何有关我如何解决此问题的想法。
兰迪
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.
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, ...);
...
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.
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.
Randy
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
子插入将看到任何已提交或先前由同一事务插入的父级（无论是否已提交）。
需要验证的一件事是父项的插入是否自动派生主键值（例如通过触发器）。
也就是说，您发出声明
INSERT into Contracts (contract_sequence_number, ...) 值 (10437, ...);
但是触发器从序列中确定一个新的contract_sequence_number，并实际上为其提供主键10438（或其他）。
另一个问题可能是任何 ORM 层不按正确的顺序发出插入或在池中使用不同的连接来执行单个“事务”，从而使问题变得混乱。
还要检查父级的插入没有返回错误。
尝试通过传统客户端（例如 SQL*Plus）执行示例事务，看看是否有效。如果子插入失败，只需从合约中查询最近的条目（例如，其中contract_sequence_number > 10400）并查看插入是否成功。
The child inserts would see any parent that has either been committed or has been previously inserted by the same transaction (whether committed or not).
One thing to verify is whether the insert of the parent is automatically deriving the primary key value (eg through a trigger).
That is, you issue the statement
INSERT into Contracts (contract_sequence_number, ...) values (10437, ...);
but a trigger determines a new contract_sequence_number from the sequence and actually gives it the primary key 10438 (or whatever).
Another issue may be any ORM layer that is mucking up the issue by not issuing the inserts in the correct order or using different connections out a pool for a single 'transaction'.
Also check the insert of the parent didn't return an error.
Try doing a sample transaction through a conventional client (such as SQL*Plus) and see if that works. If the child insert fails there, just query back the most recent entry from contract (eg where contract_sequence_number > 10400) and see if the insert succeeded.
将您的 fk 约束设置为
可延迟初始延迟。此外，您需要在单个事务中执行两个/所有插入。如果您使用 C# 的 ODAC，则首先开始 OracleTransaction，执行插入，然后 commit() 和 dispose()。确保在 catch 块中为事务调用 rollback() 和 dispose()。
有关 Oracle 数据访问组件文档的 PDF 版本，请参阅此处 （11 克）。
希望有帮助
Set your fk contraints to
deferrable initially deferred.Also, you need to do both/all inserts inside of a single transaction. If you're using ODAC for C#, then first begin an OracleTransaction, do the inserts, and then commit() and dispose(). Make sure in catch block to call rollback() and dispose() for the transaction.
See here for PDF version of Oracle Data Access Components documentation (11g).
Hope that helps
是否有任何插入是通过 PRAGMA_AUTONOMOUS_TRANSACTION 设置完成的？在我看来，待处理的提交应该对整个事务可见。
哦，另一个想法 - 如果插入位于 PRE 触发器中，请尝试将它们移动到 POST 触发器中。
are any of the inserts done with a PRAGMA_AUTONOMOUS_TRANSACTION setting? it would seem to me that the pending commits should be visible to the entire transaction.
Oh another thought - if the inserts are in a PRE trigger, try moving them to a POST trigger.
我建议您检查延迟约束，它将在您将其提交到数据库时检查值，这样您就可以避免 FK not found 错误
i recommend you to check deferred constraints which will check for values when you are committing it to the database, so you would be able to avoid FK not found error
感谢大家的意见。事实证明（正如我所怀疑的）这是我的错。事实证明有两个非常相似的模式，并且我使用的连接可以访问这两个模式。两个模式都有同名的表。由于我不清楚的原因，父级正在插入一个模式，但子级试图插入另一个模式。当然解决不了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!
Again, thanks.