On Sun, Jul 6, 2008 at 7:33 PM, Berend Tober wrote:
Jack Brown wrote:
I need some tips and/or pointers to relevant documentation implementing
(what I chose to call) "a perfect sequence" i.e. a sequence that has no
missing numbers in the sequence. I'd like it to auto increment on insert,
and auto decrement everything bigger than its value on delete. There are
many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure
which combination would result in the most elegant implementation.
Oh, and if you know the right term for what I just described, I'd be more
than pleased to hear it! :-)
This question comes up a lot. A term used in prior discussions is "gapless
What would be really more interesting for discussion on this community forum
is a detailed description or your actual use case and requirements.
I will say that if you need a gapless serial numbering system it's
still better to NOT try and do it with a pre-checked out number. For
instance, you might have a system like a court document system that
might have this requirement, that you hace CR-1 through CR-99999999 or
In that case it's better to let the user start work, then hit CREATE
DOCUMENT when they're ready. Then your business logic can put the
data into the database, and if it goes in, then check out a number
from the sequence. I.e. there are no deletes, only failed inserts. A
system that requires you to show a number before the document has been
"created" in the system but wants no gaps is flawed. Don't give them
a number until they HAVE a document. reusing numbers already shown to
a user is a recipe for a disaster. they write down the number, and
two weeks later reference it, but it's not there.
That's one use case. It's important here to look for the way that is
less likely to lead to "oh crap!" moments.
Adding gapless sequences increases the complexity. Better to let the
complexity only live in a display layer of sorts than to rely on it
for FK-PK type stuff.
If there's any FK->PK relations involving these keys and they aren't
fully cascaded, then allowing them to be renumbered is courting
disaster. If you use a separate table for "user visible sequence
number" and store the plain sequence, gaps and all in the db, then
your actual core data is safer. You can recreate the user visible
sequence number table without affecting the actual relationship of the
data in the real data table.
I hope I'm not rambling too much.