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
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.
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
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.
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
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.
