Re: Primary Keys

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