primary key generation

[thomashk]

Is there a way to generated the primary key of a new record automatically. If so, can I also get the generated value back via the JDBC driver?
Is it possible to use sequences (or something simular) in progress?

 

[Sebastian SITC]

Create a sequence with the data dictionary, then, in your create trigger, you assign the value to the primary key:

ASSIGN Table.PrefixID = NEXT-VALUE(SequenceName).

 

[BONO]

hello,
think about use include to code it and pass field as param, then u use same include for all u're triggers. Th day u'll want to add something u'll just have to modify the include

 

[MurrayH]

If you want to never see it in the code you can add it to the create trigger on the database table - I think the example Sports database does this for the customer table.

 

[jongpau]

I think it would be better to do it in a write trigger of the database table - first checking whether the record is new before assigning the next sequence value and if not a new record do nothing. This will make sure that the sequence is only used and increased when the record is actually written to the database (a create trigger fires on create of a record and is not undone when the transaction fails).

Also, keep in mind that the maximum number that can be generated by a sequence is limited (not sure what the limit is from memory, but I am sure you can find that in the Progress docs).

 

[Sebastian SITC]

Depends

If the number of unique values per sequence is a concern, I'd go for UUIDs. Actually, each sequence can generate 4 billion values (-2147483648 to 2147483647) while the limit per table is 2 billion tuples or 256 gigs. Thus, if you use one sequence per table, usually you won't run out of unique values. If you have more than 2,000 tables (max. number of sequences per database with block size 8192), I'd go for UUIDs too. Sequence values do not get reset if a transaction fails, so they are lost anyway.

Assigning the next sequence value in the create trigger has the advantage, that you can do something with the primary key value immediately after the insert, e.g. assigning it to (new) childs.

 

[MurrayH]

No - I disagree - thats adding a "read" check to every write to the database - way too much overhead. you can use 2 sequences together. I think we worked it out it would take you hundreds of years to run out at about 60-100 creates per second.

 

[jongpau]

But, if the transaction gets undone when using a create trigger, the sequence does not get reset. This means you might get "holes" in your numbers, which a lot of people (read users/customers) would find unacceptable...

 

[Sebastian SITC]

Sequences are meant to produce technical keys, which are mostly invisible to users. They aren't reset if a transaction fails. If you need consecutive numbers, e.g. invoice numbers, you need to increment an integer attribute of a table within the transaction.

 

[BONO]

For sequence limit if you have prefix table name, (like TDT for Table Technical Data, or TPA for Table Param ...) u can use a sequence by prefix also ...
So we use it on create trigger. The idea is good for "write trigger" so it depend of the use. We use sequence for technical key and we store other critical number on a specific table, like Sebastian SITC said.