Обсуждение: 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?)