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

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'm a bit bit confused is a foreign key can (should) be declared as type
integer or serial.

Thanks!

jc

Hi Jean-Christian,

You should use integers in the table that has the foreigh key.  The table
that has the serial type field is usually the parent table and has the
primary unique key.  When you setup the parent table a sequence is built
that auto increments and place the unique key obtain by the sequence
function into the parent table.  The serial type field will create an
integer 4 or integer 8 as the primary field for the parent table.  An
example of a parent table is a table of customer where each row holds info
about the customer including the customer's unique primary key.

An example of a table that would hold the foreign key would be the
customer's transaction table where each row hold information like payment,
amount order,  and cost.   The foreign key table can have many row that
contain the same primary key that would indicate which customer owned the
transactions.  If you may later want to place a constraint on the foreign
key into the parent table's primary unique key, be sure that the parent's
primary key and the child's primary key are the same.  For example, if the
parent's primary key is set to integer 4 with auto increment from a
sequence, be sure the child's foreign key is also set to integer 4.

----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Saturday, August 03, 2002 8:56 PM
Subject: [GENERAL] Maintainability: is declaring serials as integers a
problem?


> 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'm a bit bit confused is a foreign key can (should) be declared as type
> integer or serial.
>
> Thanks!
>
> jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

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