more-than-one-SERIAL column per table 
Author Message
 more-than-one-SERIAL column per table

Quote:

> > \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.***.com/


http://www.***.com/ !
http://www.***.com/ !

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Mon, 27 Oct 2003 02:48:18 GMT
 more-than-one-SERIAL column per table

Quote:


> > > \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,
--

Director of Information Systems, Support Center of Washington

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Mon, 27 Oct 2003 03:03:57 GMT
 more-than-one-SERIAL column per table
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

Quote:


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 27 Oct 2003 03:14:36 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

2. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

3. ALTER TABLE ADD COLUMN column SERIAL -- unexpected

4. ALTER TABLE ADD COLUMN column SERIAL -- unexpected

5. ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

6. inserting multible columns from one table into a single column in another table

7. DBList with more than one item per column

8. one table - no dups in one column

9. more than one fti per table

10. Compare column name in one table to a column value in another

11. one index per table

12. Script to copy data from one existing column to a new column in another existing table


 
Powered by phpBB® Forum Software