Oracle APEX Creating surrogate keys for composite PK

Question

I have two tables I need to process.

One table "Delivery" contains id of delivery as PK, id of storage to deliver as FK and id of storekeeper as FK.

The other table "Delivery_log" contains composite primary key, which consists of id of delivery and id of book, that have been delivered.

So, my task is to create convenient Interactive Report-based page to add and edit these two tables.

How it should work:

1. First of all, first page should show content of "Delivery" table. It works fine with basic Interactive Report template;
2. Then, ther should be function to add new row to "Delivery" table. It works fine as well;
3. You should be able to edit these rows as well, but standart Interactive Report template does not work here, because when we click on edit icon that is on the left from the id column, I want to another page to be appered, which is also based on Interactive Report and which displays the content of "Delivery_log" table, where id of delivery is equal to id of delivery, that we chose on previous page, when we clicked on edit icon;
4. Second page also must contain Create button to add new rows in the "Delivery_log" table
5. And finally, there should be an edit form when we click on edit icon, but the fact that "Delivery log" table contains composite primary key makes it more difficult, because it means I have to create a surrogate key for each row of that table to be able to change id of book which is part of composite primary key.

1, 2 and 4 list items I imagine how to do, but questions are left about how to change the page that edit icon will redirect user to. I looked all over the Interactive Report generated page and I didnt find any customisations of edit icon at all.

And second question is how to create surrogate key for the "Delivery_log" table to be able to edit and add rows.

Answer 1

As delivery and delivery_log make master-detail relationship, maybe the simplest option is to use the Wizard and create page that reflects that relationship; you'd get two interactive grids on the same page, fully functional.

---

If you want to do it with an interactive report and a form (which is what you currently have), then

• default functionality should be left for the delivery table:
  • "Create" icon (upper-right) creates new deliveries
  • "Edit" icon at the beginning of every IR row edits data related to that delivery

For delivery_log, there are two options:

• one is to put its interactive report to form which is used to maintain delivery info; there you'd have all buttons you need ("Create" and "Edit") which lead to the second form (based on delivery_log table)
• another is to edit the delivery interactive report and add a new link column which leads to interactive report based on another [interactive report + form] pages set, both based on delivery_log

---

As of a surrogate key column: I'd use a sequence (or identity column, if your Oracle database version supports it).