Re: generating part of composite key

On Jul 26, 2007, at 5:57 PM, Stuart wrote:
> I have a table with a composite PK like
>
> CREATE TABLE t (
>     grp INT NOT NULL,
>     itm SMALLINT NOT NULL,
>     ...,
>     PRIMARY KEY (grp,itm));
>
> Normally the app takes care of providing the correct
> grp,itm values when inserting records.  However
> (during a long period of development), I need to
> repeatedly reload data into the table from a data
> source (a select statement) that has grp values but
> no itm values.  These itm values need to be small
> numbers (1 to COUNT(itm) for each grp value) and
> capture the order in which the data was generated
> by the select.
>
> MySql seems to have an auto_number function(?) that
> takes an optional argument which would be grp in this
> case, that (judging from the manual, I don't actually use
> MySql) gives the behavior I want (restarts numbering
> from 1 when grp value changes).

I'd recommend writing a function in a language that allows you to
store state information between calls, such as plperl and have it
handle the counting, reseting the count every time grp changes. Of
course that means you need to order by grp in your select (and grp
has to be the first sort key). If you can't do that, your next best
bet is to populate itm with a sequence (not resetting) and then
adjust itm after the fact by selecting min(itm) ... group by grp.
Might want to do that in a temp table to avoid bloating the main table.

Note that anything that involves resetting a sequence or anything
like that is going to be a big race condition if you have multiple
inserting processes.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)