Re: Maintainability: is declaring serials as integers a problem?

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> I have tables that have seirals as primary keys. Other tables uses these
> as foreign keys. In terms of future maintainability is it best to
> declare the foreign key as:
> 1- id  integer  references other_table
> or
> 2- id  serial  references other_table

I concur with Leland: declare the foreign key as integer (int4), or
bigint (int8) if you're using bigserial (serial8).

The way to look at this is that serial is just a shorthand for creation
of a sequence object and setting the column's default to "nextval(seq)".
For a foreign key the sequence object is useless overhead, and the
default is probably actively dangerous: you do NOT want the foreign key
column to be generating default values, especially not ones that are
coming from a sequence object unrelated to the referenced column's
sequence.

BTW, serial also implies NOT NULL and UNIQUE constraints on the column.
These may or may not be appropriate for your foreign-key column, but if
they are, you can certainly put 'em in by hand.

The bottom line here is that "serial" is a macro for several concepts
that commonly go together.  Use it when it's appropriate, but don't be
afraid to look under the hood.

            regards, tom lane