Re: Equivalent for AUTOINCREMENT?

Halle Craig,

Am 2008-11-05 20:37:31, schrieb Craig Ringer:
> If you really, truly need gapless sequences, there are some options. I
> posted about them recently on another thread. The archives will contain
> that post and many others from many people on the same topic. Be aware,
> though, that gapless sequences have some NASTY performance consequences.

Since this "NASTY performance consequences" would only hit the INSERT
statement and it is very unlikely that I have concurence WRITE/INSERT
access, it is a minor problem.

> Design your application not to expect your primary keys to be gapless.
> If it requires contiguous sequences for something, generate them at
> query time instead of storing them as primary keys. If the contiguous
> sequence numbers must also be stable over the life of the record, try to
> redesign to avoid that requirement if at all possible.

Yes it is a requirement... and this is, why I have tried to get the
highest value of the column "serno".

> CREATE TABLE id_counter ( last_used INTEGER NOT NULL );
> INSERT INTO id_counter ( last_used ) VALUES ( -1 );
> --
> UPDATE id_counter SET last_used = last_used + 1;

> --
> INSERT INTO sometable ( id, blah ) VALUES ( (SELECT last_used FROM
> id_counter), 'blah');

Thank you for the example....
I will try it out now.

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)