Scott Marlowe wrote:
If you need a unique constraint on the text field anyway, and it's a natural key, you're generally better of using that field as the pk. However, if it's not a natually unique key, then it shouldn't be the pk, and int is a perhaps better choice. There are two VERY oppositional schools of thought on natural versus artificial keys out there, and neither side is likely to change their minds. My preference is generally for artificial keys (i.e. sequence generated ones) because I've had requirements change underfoot too many times to rely on natural keys all the time.
Thanks for your answer. It sounds like your saying that in terms of performance, there is no difference between a character field pk and an integer pk.
I've got a followup - The primary key for the table in question consists of 2 varchar fields: picture 'state' and 'city' where city is guaranteed to be unique within a state, and (state, city) form a unique key. This sounds like a good candidate for a sequence key. Is there a difference in terms of performance in this case?
