1 person found this helpful
"... SELECT MAX( <pk_column_name> ) + 1 ..."
*Never* do it this way. It is worst praxis ever to get garantied PK values.
Even when you have done it befor - it does'nt make that better.
Use a Sequence and trigger (or a table API) or if you are on12c AUTO_INCREMENT -> https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/example-auto-increment.html)
I have to apologize, here an adjustment:
The link above points to the MySQL documentations.
The syntax for the autoincrement feature in oracle DB (=Identity column) is different:
Thank you Sven for pointing out my mistake!
ROWID is a completly diferent story.
It looks good at the first glance; and surely there are situations where it make sense using it. However it is NOT a valid substitution for a PK that every table should have.
1 person found this helpful
Apex in some scenarios relies on best practices.
It is best practice to have a primary key that is populated "automagically" by the database.
Usually by a sequence generator + database trigger to put the generated values into the PK column.
Apex then is able to fetch this value useing the return clause during an insert statement.
IGs can do this too. But they have some more options. However the final insert is only done when the submit button was pressed and all the validations went through.
So there shouldn't be a validation that requires a NOT NULL value in the PK column.
If you create a table using Apex SQL workshop, then you have an option that exactly this best practice is used to generate your IDs.
Also make sure your column information is correctly set:
The link you supplied points to the MySQL documentations. The syntax for the autoincrement feature in oracle DB (=Identity column) is different:
If you generate the table by hand, make sure you (readers) define the identity portion as:
GENERATED BY DEFAULT ON NULL AS IDENTITY
APEX has a habit of sending NULL values for the PK and assumes that the "existing trigger" will take care of it.
I have found that, by default, the "ON NULL" portion is not enabled which will cause APEX to "fail".
As Sven W. said, APEX gets the value back into a Page Item via
RETURNING pk_column INTO :P99_PK_COLUMN
Thanks to everyone for their prompt replies, the message I'm getting is use a sequence ( as I'm not on 12c ). When you mention that APEX gets the value into the 'Primary Key' Page item using 'RETURNING pk_column INTO :P99_PK_COLUMN' I'm assuming that this is from the PL/SQL code process automatically generated by the Page wizard,
I understand that it is best practice to use a sequence or trigger to populate the primary key when using an Interactive Grid but I'm really curious as to why choosing the rowid option allows me to add a new row to the database (from an Interactive Grid) without any additional functionality being added to the Page,
to be very honest I don't use IG by now - except for a little private investigation and testing. The reasons for that are out of scope in this context....
So my experiance (for effective use) is rather low.
I do think your question can be answered best by someone from the Dev's or other more trained folks.
Who are the devs? Is this the Apex development team and can they be contacted directly?
Yes, by saying "Dev" I mean the developement team.
It's very likely that they scan the discussions here.
However, I cannot say what they are picking up and what not - and when.