Re: Primary Keys - Mailing list pgsql-novice

From Josh Berkus
Subject Re: Primary Keys
Date
Msg-id 200311091333.47091.josh@agliodbs.com
Whole thread Raw
In response to Re: Primary Keys  (Nabil Sayegh <postgresql@e-trolley.de>)
List pgsql-novice
Nabil, Farid,

> Personally I would always choose an extra SERIAL column.
> I don't really like the concept of having multiple columns as a PK.

I really have to disagree with you; if the real key of the table is two
numeric columns, then make that the primary key unless you can demonstrate
significant performance or application development problems.

For one thing, a serial column adds another 4 bytes per row to the table, and
overhead on inserts.   It also raises the possibility of duplicate columns
X,Y; and if you put a unique index on X,Y, why bother with the surrogate key
at all?

I've a lot of tables with SERIAL surrogate keys, but those exist only for 3
reasons:
1) The real key involves several columns, including text and dates, that would
be a real query-writing hassle and/or potential performance-killer on joins;
2) Or where my web programmer refuses to deal with non-numeric keys (which he
frequently does ...)
3) The values of the key in existing rows is expected to change frequently and
there are FK-dependant tables (cascading updates are another
performance-killer).

Where none of those conditions apply, I use real keys.

--
Josh Berkus
Aglio Database Solutions
San Francisco

pgsql-novice by date:

Previous
From: Nabil Sayegh
Date:
Subject: Re: Primary Keys
Next
From: Josh Berkus
Date:
Subject: Re: The data directory was initialized by PostgreSQL version 7.4, which is not compatible with this version 7.3.3.