Re: more-than-one-SERIAL column per table

On Wed, 9 May 2001, will trillich wrote:

> On Sun, May 06, 2001 at 02:59:31AM +0200, Morten Primdahl wrote:
> > > \d tbl_c_id_seq
> > >
> > > if it is not there, you can create it yourself
> >
> > Thanks, the sequence was not there. Just puzzled me that
> > when creating tbl_c, I get:
> >
> > test=# CREATE TABLE tbl_c
> >  (id SERIAL PRIMARY KEY,
> >   data VARCHAR(50),
> >   a SERIAL CONSTRAINT a_ref REFERENCES tbl_a(id),
> >   b SERIAL CONSTRAINT b_ref REFERENCES tbl_b(id)
> > );
>
> is there any paradigm wherein TWO serial values for one table
> might possible be useful? (since serial is really "int default
> nextval('sequence_seq')" how can the second serial be anything
> but redundant?)

Well, given that you can set the sequence value yourself, and it doesn't
have to be unique, it *could* be useful.

Suppose you want to keep track of how many projects you did since you
started the company, and how many you did since you since you last
rebooted the server. You could have a SERIAL for the table that starts
counting at one, and just keeps counting, and have a different SERIAL that
you reset (via setval() every time you reboot.)

There are other ways of handling this, of course, count() and such, but in
some cases, this might be a decent solution to that problem, especially if
your client application is too dumb to use grouping queries or such.


In the case of the original question, I think he was confusing using a
serial-as-primary-key with using serial-as-foreign-key. Perhaps I lack
imagination, but I can't see any good reasons to use a serial as foreign
key.

(well, maybe I can. What if you had 100 prizes to give out to the first
hundred callers. you have a table with prizes, using a int SERIAL as the
primary key. in the callers table, you use something else as the primary
key--perhaps the callers phone number or somesuch. you have a serial
references to the table, so that you automatically link each new caller
with their prize. this also blocks entries after the first 100. i don't
think i'd do it this way, but it might be defensible.)

HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington