Insert parent / child records in a transaction

Question

I have a C # application that should insert one parent record and at least four children records in a hierarchical order. IOW, the parent contract applies to one or more locations, each location has one or more elements, each element has one or more services, and each service has one or more requirements. The application first receives a set of Oracle sequence numbers, one from each sequence of tables for each record. For some reason (outdated database), each record has not only its parent serial number, but also the serial number of the contract.

So, the code starts the transaction, inserts the parent with the parent number of the parent level, then tries to insert the location record already populated with both the parent number and FK, and its own table serial number. However, I get an Oracle-02291 error that the FK violates because the parent number was not found.

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

I suppose this is because the parent was not perfect and therefore unavailable. However, I cannot commit the parent if any of the child records failed, so there is no commit before inserting the child.

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

Any thoughts on how I can fix this are greatly appreciated.

Randy

Answer 1

Thank you all for your input. It turned out (as I suspected) that it was bad. It turns out there are two very similar schemes, and the connection I used has access to both. Both schemas have tables with the same name. For reasons not clear to me, the parent inserted into one sheme, but the child tried to insert into another scheme. Of course, this could not solve the PK / FC relationship!

Thanks again.

Answer 2

The child inserts will show any parent that was either committed or was previously inserted by the same transaction (regardless of whether it was executed or not).

You can check whether the parent insert automatically displays the primary key value (for example, via a trigger).

That is, you issue the INSERT expression in Contracts (contract_sequence_number, ...) of the value (10437, ...);

but the trigger determines the new contract_sequence_number from the sequence and actually gives it the primary key 10438 (or something else).

Another problem may be any level of ORM that fixes the problem without giving out inserts in the correct order or using different connections from the pool for one transaction.

Also check that the parent insert did not return an error.

Try a selective transaction through a regular client (e.g. SQL * Plus) and see if this works. If a child insert is inserted there, simply request the last record from the contract (for example, where contract_sequence_number> 10400) and see if the insert succeeded.

Answer 3

Set fk loops to deferrable initially deferred .

In addition, you need to do both / all inserts within the same transaction. If you are using ODAC for C #, first start OracleTransaction, do insertions, and then commit () and dispose (). Verify that the catch block calls rollback () and dispose () for the transaction.

See here for the PDF version of the Oracle Data Access Components (11g) documentation.

Hope that helps

Answer 4

- any inserts made with the PRAGMA_AUTONOMOUS_TRANSACTION setting? It seems to me that pending commits should be visible throughout the transaction.

One more thought - if the inserts are in the PRE trigger, try moving them to the POST trigger.

Answer 5

I recommend that you check pending constraints that will check values ​​when they are passed to the database so that you can avoid an FK error not found