Re: serial autoincrement and related table

On Mon, 17 May 2004, Milos Prudek wrote:

>
>  > Actually, if you declared idmember as SERIAL PRIMARY KEY, you could
>  > just do:
>
> I can't do that. idmember is a SERIAL PRIMARY KEY for members. Each
> member can have many messages (msg table) with the same idmember column
> value. See my original post.

Oh, sorry I got it now. Then, you may want to use currval() just as
Mr. Richard Huxton wrote. You can still play with DEFAULT of course,
but this may be more readable or not depending on your personal taste:

create table members (
idmember serial primary key,
some_data text             -- dummy data
);

create table msg (
idmember int references members default currval('members_idmember_seq'),
txt text
);

insert into members (some_data) values ('member foo');

insert into msg (txt) values ('some text for foo');
insert into msg (txt) values ('more text for foo');

insert into members (some_data) values ('member bar');

insert into msg (txt) values ('some text for bar');
insert into msg (txt) values ('more text for bar');

After i run the script, i get:

marco=# select * from members;
 idmember | some_data
----------+------------
        1 | member foo
        2 | member bar
(2 rows)

marco=# select * from msg;
 idmember |        txt
----------+-------------------
        1 | some text for foo
        1 | more text for foo
        2 | some text for bar
        2 | more text for bar
(4 rows)


See how there are no references to the sequence name in the application
code (which is, IMHO, good).

One word of warning. You can't use currval(), either explictly or
implicitly, alone in a session. You need to call nextval() first.
This is not your case, as you seem to do always an INSERT in members
before the ones in msg.

.TM.
--
      ____/  ____/   /
     /      /       /            Marco Colombo
    ___/  ___  /   /              Technical Manager
   /          /   /             ESI s.r.l.
 _____/ _____/  _/               Colombo@ESI.it