Re: serial nextval() question

On Fri, Aug 16, 2002 at 18:11:45 +0900,
Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> wrote:
>
> I'd like to have li_id be an serial but not for the whole table, only as
> li_id relates to cart_id ... i.e. I'd like li_id to increment relative
> to cart_id so that I can generate sequences of the kind:
>
> cart_id li_id
> 1 1
> 1 2
> 1 3
> 2 1
> 2
> 2
>
> How could I do this?

It doesn't sound like you want to use serial for this application.
serial gives you unique monotonicly increasing values. It doesn't
guarenty that there won't be gaps. You would need to have a separate
sequence for each value of cart_id, which isn't a good idea.

You can implement something like the above using sequel by using max or
count to figure out the next number. (Just remember to do select for update
when doing the calculation.) You can use a contraint to make sure
li_id, cart_id pairs are unique.

This is slower than using serials, but that problem can usually be solved
with hardware.