ADF Magic or Bad Database Design for Master-Detail - Editable Primary Keys

While helping Oracle Forms customers to modernize their systems into Oracle Fusion, sometimes we face interesting requirements. Together with my colleagues from Cape Town (South Africa) we are implementing Oracle Fusion system based on database design inherited from old Oracle Forms system. One of the most common use cases there - Master-Detail relationships across different levels. Some of the Master-Detail use cases are using editable primary keys. This means there is no database sequence for primary key, user types key value directly from interface. And its where problem starts - because we are using incorrect design and asking user to provide primary key values manually, things are not working as expected. But what to do, sometimes we can't change design and are forced to find solutions. Today I will describe what type of problems you will face with this use case, and how to solve it with a bit of ADF magic. Its where ADF magic comes - even we are using wrong database design, we still can make it work with ADF.

Download working sample application - CreateMasterDetailNoSequence.zip. This sample demonstrates how you can insert new records into Master and Detail entities, without using auto generated sequence numbers. I want to stress again - its not recommended, but we can't change DB design :-) There is no DB Sequence, and no Triggers - user types primary key value manually.

First we create new Master record - new Region:

New blank record is inserted into Regions table, with editable primary key - RegionId. Let's type key value - 99 and provide RegionName as well:

Without saving Master record, insert new record into Detail table - Countries:

You can see that foreign key - RegionId, is not populated for Countries. This means we can't save Master-Detail data, because integrity is broken. If we would use auto generated database sequence values, it would work perfectly. But now, we need to apply ADF magic - let's move Create Country button from Detail block into Master block.

I assume, when create button for Detail is above Master, it somehow submits entered Master key value, before actually triggering Detail block. Its why it works now - I press Create Country button and it successfully inserts new record with foreign key populated:

Both - newly inserted Master and Detail can be commited to database together:

Just make sure that Create button for Detail entity is set as Partial Trigger for Detail Panel Collection component:

I highly recommend to use auto generated values for primary keys, always avoid editable primary keys. If there is possibility, better to define surrogate keys.

Described behavior happens with Master-Details implemented in ADF Fragments. It doesn't have this side effect, when using simple pages.