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@scw.org>
Director of Information Systems, Support Center of Washington