Re: pseudo-serial values in dual primary key?

On Sat, Mar 19, 2005 at 01:43:07 -0800,
Benjamin Smith <lists(at)benjamindsmith(dot)com> wrote:
> Is it possible to have the equivalent of a serial data type in a table,
> sub-categorized?

[snip]

> Now, I want to create an entries table, and by default, count serially by
> category, so that category 1 has entries.sequence of 1, 2, 3, and so does
> category 2. (where sequence= 1, 2, 3...) Something like:

[snip]

> I'm not sure about the semantics of this, but i want sequence to start at 1,
> and count up, for its category as defined by categories_id. I already know
> that I can set enforce the uniqueness of categories_id and sequence with thte
> primary key, and I could just write some more app code to do a query to get
> the max value of sequence where categories_id=$categories_id, but can this be
> done without adding a bunch of xtra application code?

Serials should be used for obtaining unique values. Within a single session
you can also depend on them to increase (unless you have set the serial
to allow for wrap around). There can be gaps. Different sessions may get
values out of order in time.

If you aren't deleting records or updating the sequence values, then the
simplest thing to is to lock the table, find the maximum value for a category
(using order by and limit instead of the max aggregate if you have an index
on category and the sequence) and use the next integer for the sequence
value.

However, it may be worth asking why you want to do this? Why not just use
one sequence over the whole table. You can still use that for ordering
within a category, and if necessary the application can renumber when
outputing data.