I am using Apache Derby which uses the following syntax to specify
database generated unique key fields:
CREATE TABLE MYTABLE (MYTABLE_ID INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY, ...);
I use the Derby function IDENTITY_VAL_LOCAL() to obtain the value of
MYTABLE_ID for a newly inserted record.

This method seems to be restricted to IBM DB2 and Derby. Is there a
database independent way of obtaining the generated key after the
insertion? I can live with different syntaxes for specifying the
generated key.

That is not how RDBMS works. By definition, a key is a subset of the
attributes of an entity. The entity cannot exist without it. Your
"generated key" is based on the internal state of the hardware at
insertion and has nothing whatsoever with a valid data model.

When you look for a key, start with your industry standards. I would
estimate that this covers ~80% of the properly defined systems -- VIN,
ISBN, UPC, EAN, etc. which can be verified and validated via a trusted
exernal source. In the old days, this took time; you can Google it
today.

After that, there is a natural key in the data, such as
(longitude,latitude), (store, cash register, ticket_nbr), etc. which
can be verified and validated in the reality of the data. This is ~18%
of the cases. if you have no natural key, then your schema is probably
wrong.

Then, if that fails and we are lokking at <2% of all situations, we
invent a key with check digits, validation rules and an audit trail
within our enterprise. This is a great screaming pain IF you do it
right. This is why industry standards exists -- people got tired the
pain Would you like to do Retail without standard UPC barcodes on
products? We just that for centuries before barcodes.

So, newbies prefer to do it wrong by using auto-increments or other
proprietary stuff that cannot be verified or validated BECAUSE it is
easier than real RDBMS design. They want to have a magical, universal
"one-size-fits-all" answer that does not require you ACTUALLY
understand the problem domain.

--CELKO-- wrote:
I completely agree that during logical design one has to find all
natural keys, because otherwise one is risking to get duplicated
records from the business viewpoint and the overall modeli won't be
correct. But using natural keys as primary keys in the real
implementation is completely conflicting with my experience until now.
I'm using surrogates everywhere except for very rarely cases, for
example, already existing systems with widely used natural keys.

Speaking about the original question of database independent way to get
generated key values - if you want your solution to scale you have to
use each database's own solution for this i.e. sequences in Oracle,
identity in SQL Server and so on. At least using some home grown pk
generators in Oracle will definitely introduce a serialization point
and in case of big insert activity you'll definitely have problems. I
assume the same thing can happen in other DB's.

Gints Plivna
http://www.gplivna.eu

"David Segall" <david (AT) address (DOT) invalid> wrote

Most databases use a function to generate the primary key if by sequence of
UUID. The way the logic works is that during record insertion, if the field
is null, then a function is called to populate the next in the sequence or
to generare the UUID. If the value is not null, then it is assumed that the
value has already been generated.

Most databases will allow you to declare a variable, call the next sequence
or newid() function to generate this. Therefore, you will have the value
prior to saving the record.

AMO

David Segall wrote:
If you are using java I think (havent tried) that the following should
work:

stmt.executeUpdate(<sql-stmt>, Statement.RETURN_GENERATED_KEYS);
rs = stmt.getGeneratedKeys();
if (rs.next()) {
java.math.BigDecimal x =rs.getBigDecimal(1);
...

/Lennart