Re: data dependent sequences?

"Andrew Sullivan" <ajs(at)crankycanuck(dot)ca> wrote in message news:20070717150046(dot)GC21688(at)phlogiston(dot)dyndns(dot)org(dot)(dot)(dot)
> On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote:
> > I am not looking for gapless sequences. The reason I
> > want to do this is the "typ" column is actually an indicator
> > of the source of the rest of the infomation in the row.
>
> Why do you need the sequence to be 1. . .n for each typ, then? If
> they're just there to preserve order, one sequence will work just
> fine. Otherwise, I think you have a normalisation problem.

I probably shouldn't have indicated that 'typ' was part of
the PK, or named the other column 'id'. There is actually a
separate (surrogate) PK, and there is a unique index on the
on (id,typ) only to prevent accidental dupicates. So 'id'
is not really structually important -- it is a value that
exists soley for the UI. In the app, the user can explicity
request an explicit 'id' value. My desire to use a sequence
to assign them is to handle the 99% common case where the user
doesn't care about assigning a specific id, and just wants
the "next" resonable value, consistent with the other values
for that typ row. If there are 3 rows of typ=20 and 2000000
rows of typ=21, I don't want the next typ=20 row to get an
id of 2000004, when the other rows have values of 1,2,3.
This is simply a user expectation, based on existing data,
that I can't change. I would just prefer to implement it
in the database if possible rather than than the app.
Hope I have clarified a little :-)