Re: integer id columns for all tables

garhone wrote:
> Hi,
>
> Someone with greater expertise than I recently told me that it is best
> to have an integer primary-key for all tables in a database, even if
> the table already has some non-integer primary key, or some sort of
> composite primary key. If there is already a non-integer key, then
> create a new sequence column and set that as the primary key.
>
> Does anyone have an opinion on this and why?

All the items one discusses have some form of identifier that makes them unique and allows us to recognise them. Use that if at all possible.

However, /in some cases/ surrogates may be useful given what one wants to achieve. If for example obtainable identifiers are particularly unstable, and this is an important factor in your design, then by all means apply a surrogate. But always realise the reality of what you are doing - you are adding a property to the item you are describing, and adjusting the propositions you are recording. Hence your new identifying attribute is now a natural property of the item concerned and must not be hidden from it.

Certainly don't just apply an arbitrary integer primary keys just for the sake of it. If someone has told you to do so its time to start taking their advice with a huge pinch of salt. Received on Wed Aug 30 2006 - 18:26:33 CEST