Thread: Re: Problems w. SERIAL

> \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)
);test-# test(# test(# test(# test(#
NOTICE:  CREATE TABLE will create implicit sequence 'tbl_c_id_seq' for
SERIAL column 'tbl_c.id'
NOTICE:  CREATE TABLE will create implicit sequence 'tbl_c_a_seq' for
SERIAL column 'tbl_c.a'
NOTICE:  CREATE TABLE will create implicit sequence 'tbl_c_b_seq' for
SERIAL column 'tbl_c.b'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'tbl_c_pkey' for table 'tbl_c'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tbl_c_a_key'
for table 'tbl_c'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tbl_c_b_key'
for table 'tbl_c'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE

Thanks for the tip, I'll consider this a bug and
doublecheck the notices from postgres from now on.

Morten

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?)

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

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

I see no reason why a second SERIAL column would be needed.  As in the
example for creating tbl_c, when you have a SERIAL as a primary  key,
the foreign keys that reference it should be int4 not SERIAL.
jeff


Jeff Daugherty
Database Systems Engineer
Great Bridge LLC

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?)