Subject Unique keys without gaps (was: Key violation on Primary/Unique key constraint INTEG_55/30 on table(s) )
Author Sándor Tamás (HostWare Kft.)
<<You should not even think of such an idea. You could never tell from
*this* transaction, whether *that* transaction had reduced the
generator. It doesn't matter if there are gaps. Once a unique number
is generated, it should never be recycled.
>>

I have a question: How can I get straight numbers without gaps safely? I
thought about a table, where I can store the actual value, and a field which
can be set if anybody gets the value, and increments by one. Then unsets
that field. When somebody tries to get a value, it first checks if that
'semaphore' is set.
Of course I have to do this only when somebody posts and commits the data
with that value. (like in trigger, or something).

How's that idea? Or is there a better one?

Any advice welcome.

Thanks,
SanTa