I did a forum search on incrementing keys and found a lot of references to auto-increment columns. But I'm just given a database with an int key; I guess I have to increment it myself. As usual, I need to know the new key to update the next table. Surely there's a common way of doing this?
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Joined: Jan 29, 2003
Hmmm, just heard back from the database designer. They recommend random with dup detection. I did that in the 80s when the only DB2 doc we had was "the Date book" 1st ed.
Oracle has sequences. You perform an SQL statement like "SELECT MYSEQUENCE.NEXTVAL FROM DUAL" to get the next number in the sequence and increment the sequence in one atomic operation. MySQL has auto-increment columns. Other databases have other ways to do this. As far as I know there is not one common, standard mechanism to do this.
The problem with generating keys is that getting the value and incrementing it has to be done as an atomic operation (if there are multiple threads or processes doing updates at the same time), so that no duplicate keys are generated.
I'm stuck with what the DA will give me, which is an int field, period. As noted above he recommended random with dup detection, and sent me some code from another project that tries up to 100 times to generate a new unique key. They needed it for a 2 digit int key once.
In DB2 6.1 and above you should be able to use the generate_unique function.
INSERT INTO table VALUES (GENERATE_UNIQUE(),'000020','Update entry 1...'); INSERT INTO table VALUES (GENERATE_UNIQUE(),'000020','Update entry 2...');
Also, regarding the Oracle sequence value advice. Your code only is syntactically correct, but it is only valid, if a sequence is available to you in your schema.
A little extra FYI on sequences, all RDBMS systems I have been exposed to (Oracle, PostgreSQL, DB2, MySQL, etc.) all support sequences. I'm not sure why they have chosen not to include one in this database, but the function will resolve the issue for you and will always return a unique key.
Joined: Jan 29, 2003
Thanks for Generate_Unique(). I'll read up on it. If I can get the value back after the insert I'll be golden.