SQL ordering and foreign key constraints

[Reece Garrett <RG...@co.pierce.wa.us>]

Hello,

I came accross a problem where my foreign key constraints were being violated because of the SQL insertion order during merge operations. I am aware that OpenJPA does not do any SQL re-ordering to avoid violating foreign key constraints, however, it was trivial to make the needed changes in org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager. In the case of a row insert I iterate over all foreign keys defined in that row. If I find one that  is new (needs to be persisted), has an auto-assigned primary key, and cannot be null (nullable=false)  then I move the row to the bottom of the list so that it will be inserted after it's foreign keys. In the current code that determines whether a later update is needed for the foreign key I added a check to see if the foreign key's state manager had already been flushed.

The net effect is that the insertion order will stay the same unless a not-null constraint on a to-be-inserted foreign key exists. In which case, that row will be inserted after the foreign key it depends on.

I am interested in submitting my changes as a patch but having seen comments that suggest this is not something on OpenJPA's roadmap am wondering if there would be any interest.

-Reece