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