Обсуждение: serial nextval() question

I have the following table def:

create table TMP_LI (

cart_id                 integer         references TMP_CART(id),
li_id                   integer         not null,
shop_id                 integer         references CHARISMA_SHOPS(id),
prod_id                 char(12)        references PRODUCTS(id),
quantity                int2            not null,
price                   integer         not null,

primary key (cart_id, li_id)
);

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?

Thanks!

Jc

On Fri, Aug 16, 2002 at 18:11:45 +0900,
  Jean-Christian Imbeault <jc@mega-bucks.co.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.