Re: Problems w. SERIAL

On Sat, 5 May 2001, Morten Primdahl wrote:

> CREATE TABLE tbl_a (id SERIAL PRIMARY KEY, data VARCHAR(5));
> CREATE TABLE tbl_b (id SERIAL PRIMARY KEY, data VARCHAR(5));
>
> 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)
> );
>
> INSERT INTO tbl_c (data, a, b) VALUES ('c data',1,1);
>
> I get ERROR: Relation 'tbl_c_id_seq' does not exist
> even though that sequence was implicitly created upon
> creation of tbl_c - or?

re: structure of table C, I think you're misunderstand something
about SERIALs. A SERIAL is just another name for an int that automatically
has a sequence set up for it. So, if in table C, you want to store
references to tables a and b, you want you schema to be:

CREATE TABLE tbl_c (
id serial primary key,
a int references tbl_a,
b int references tbl_b
);

note the use of INTs, not SERIALs.

I don't know if that will explain the weirdness of your error message,
but, even if not, it will certainly a future error! :-)

HTH,
--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington