Subject Re: 'scoping' generator sequences?
Author Robby
Thanks, I checked out that article along with finding a previous
recent thread that someone had the same kind of problem I had.

However, from what I've seen, that article covers a way to have a
single, reusuable sequential ID pool that isn't the primary key.
This is part of what I want, but I also need a seperate pool for
each member (see my previous example in this thread). In effect, we
have two tables, member and foo, and the basic foo schema might look

memberid INT NOT NULL REFERENCES member,
UNIQUE(memberid, fooid) <<<------- this is the important part, a
composite key that end-users use to reference any given entry

Maybe I'm just being brain dead, but how could I get multiple
seperate pools for each member using that method?

After I've thought about it a bit, the best solution I've gotten was
to 1. as per that article, not use the 'member' primary key field as
my member ID field, 2. for foo, use the "NEW.fooid = MAX(fooid) + 1
WHERE memberid = NEW.memberid" generation scheme and somehow write
lock it so that no other transaction could be creating a record in
that table at that time.


--- In, Alexandre Benson Smith
<iblist@t...> wrote:
> rbd523 wrote:
> >Alright, last question for the day :)
> >
> >Say I have a table called 'member', and a table called 'foo'.
> >member may have n foo records for him (1:n relationship).
> >
> >It is simple enough to make a generator for foo that makes IDs
> >sequentially, such as:
> >
> >member 1 makes foo record, foo id 1
> >member 1 makes foo record, foo id 2
> >member 2 makes foo record, foo id 3
> >member 5 makes foo record, foo id 4
> >
> >and so on... But, how could generators be used if I wanted
> >to "scope" the foo ID spaces to a member record, such as:
> >
> >member 1 makes foo record, foo id 1
> >member 2 makes foo record, foo id 1
> >member 1 makes foo record, foo id 2
> >member 3 makes foo record, foo id 1
> >member 3 makes foo record, foo id 2
> >member 3 makes foo record, foo id 3
> >member 4 makes foo record, foo id 1
> >
> >Now, I can do this with a BEFORE INSERT trigger statement for foo
> >such as:
> >
> >NEW.myid = SELECT MAX(myid) + 1 FROM foo WHERE memberid =
> >NEW.memberid;
> >
> >But, of course I would like to use generators as that statement
> >above isn't really safe.
> >
> >Any ideas?
> >
> >Thanks,
> >
> >Robby
> >
> >
> Hi Robby,
> Take a look on the historic of the list or do a google for
> Series (or look at IBO site, this doc are somewhere on this site).
> concept can help you a lot.
> see you !
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda.
> Santo Andre - Sao Paulo - Brazil