Обсуждение: Data type for serial during constraint?

Hiya,

I've never sent mail to a list before, so i hope i get it right =)

Downloaded and installed Postgres today for Slackware, for the first time.
Kudos to the PG team; we still can't believe it went so well.  We were up,
with JDBC access in about 20 minutes.  OH, a note about that... your docs
say Class.forName("postgresql.Driver") when the package created by your
makefile is really "org.postgresql.Driver" =)

Question tho':  I've specified a SERIAL primary key ("fooID") in some table
("t1").  From another table, I'd like to specify that primary key as a
foreign key.  I have to include a data type, but that will create another
sequencer for that field, which I would imagine is not the way root
intended?  for "t2"...

CREATE TABLE "T2" ( "t2ID" SERIAL, "fooID" ???? REFERENCES "t1" ("fooID") );

What goes where the '?' are?  Thanks in advance!

- Rob S.

"Rob S." wrote:
>
> Hiya,
>
> I've never sent mail to a list before, so i hope i get it right =)
>
> Downloaded and installed Postgres today for Slackware, for the first time.
> Kudos to the PG team; we still can't believe it went so well.  We were up,
> with JDBC access in about 20 minutes.  OH, a note about that... your docs
> say Class.forName("postgresql.Driver") when the package created by your
> makefile is really "org.postgresql.Driver" =)
>
If you are referring to the output of the Makefile when you compile the
jdbc drivers that will be fixed in the next release.

> Question tho':  I've specified a SERIAL primary key ("fooID") in some table
> ("t1").  From another table, I'd like to specify that primary key as a
> foreign key.  I have to include a data type, but that will create another
> sequencer for that field, which I would imagine is not the way root
> intended?  for "t2"...
>
> CREATE TABLE "T2" ( "t2ID" SERIAL, "fooID" ???? REFERENCES "t1" ("fooID") );
>
> What goes where the '?' are?  Thanks in advance!
>
See http://www.postgresql.org/docs/postgres/sql-createtable.htm
and http://www.postgresql.org/docs/aw_pgsql_book/node152.html

For more help try the pgsql-sql mailing list.

> "Rob S." wrote:
> >
> > Question tho':  I've specified a SERIAL primary key ("fooID") in some table
> > ("t1").  From another table, I'd like to specify that primary key as a
> > foreign key.  I have to include a data type, but that will create another
> > sequencer for that field, which I would imagine is not the way root
> > intended?  for "t2"...
> >
> > CREATE TABLE "T2" ( "t2ID" SERIAL, "fooID" ???? REFERENCES "t1" ("fooID") );
> >
> > What goes where the '?' are?  Thanks in advance!

SERIAL is not really a first-class type.  Here's an example that reveals
the parser translation into an integer type:

testdb=# create table foo (id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for
SERIAL column 'foo.id'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey'
for table 'foo'
CREATE
testdb=# \d foo
                            Table "foo"
 Attribute |  Type   |                   Modifier
-----------+---------+----------------------------------------------
 id        | integer | not null default nextval('foo_id_seq'::text)
Index: foo_pkey

Regards,
Ed Loehr