Take the 2-minute tour ×

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.


share|improve this question
You can't split up your insert into two separate functions? Return the ID for the parent record to your c# code, then use that to perform the child inserts? –  TheGeekYouNeed Jan 27 '11 at 0:11
Actually, they are in two separate functions within the same transaction block. I am just showing the two SQL statements to clarify my problem. –  EoRaptor013 Jan 27 '11 at 0:15

5 Answers 5

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.

share|improve this answer
Excellent thought about the insert trigger. Although I didn't find one, I'll ask our knowledge expert. –  EoRaptor013 Jan 27 '11 at 16:44
Also, it makes sense to me that the child should see the parent ID, if it is in the same transaction. Do you know if this is actually true for Oracle? My two inserts are definitely in the same transaction, nevertheless I get the FK violated, parent not found, even though I confirm that the parent record inserted properly. How is this parent/child issue normally handled? –  EoRaptor013 Jan 27 '11 at 18:56

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

share|improve this answer
Thanks. This is pretty much what I'm doing. In C#, I begin the transaction, insert the parent, then try to insert the first child. I'm not sure I can alter the FK constraint as you suggest. DBA's sometimes aren't the most cooperative. –  EoRaptor013 Jan 27 '11 at 16:42
Oh, and yes, I do have a rollback in the catch. –  EoRaptor013 Jan 27 '11 at 16:43
Yes, DBAs should be assisting in the development effort imo (and ultimately in making the company $$$). Unfortunately some don't think this way... –  tbone Jan 27 '11 at 18:21

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.

share|improve this answer
The application is in C#, although the save functions build Oracle Command objects. I'll have to read up on PRAGMA_AUTONOMOUS_TRANSACTION. Thought that was purely an Oracle procedure thing. –  EoRaptor013 Jan 27 '11 at 0:17
the PRAGMA is not a normal thing, but it would definitely create a similar situation to what you are describing if it existed. It is probably not somehting to worry about here. If you are issuing many sequential inserts outside of a trigger, then i suggest COMMIT in between each one - that will allow visibility. –  Randy Jan 27 '11 at 17:09

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

share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer


