On Mon, 2004-07-26 at 13:08, Rafael Charnovscki wrote:
> I have a 6-digit integer number for a patient identifier which
> I intend to use as a primary key (PK) for a table. It also will
> be a foreign key in other tables. Do I have to use an integer data
> type as a domain for this PK or could I use a varchar(6)? Is there
> any difference between an index on an integer column and one on a
> varchar column?
>
> I've been searching the internet and books for something to help on
> this question but I haven't found. There are lot of good discussions on
> choosing primary keys but nothing related to my issue.
> Any suggestions or references may help.
Generally speaking, an int type will always win the performance race.
However, it may not provide the flexibility you need for certain type of
keys.
As long as the two types match, then the performance on joins should be
acceptable.
Index wise, the fields for a text type fields will almost always be
bigger than an int type would be, and will be faster. As long as the
pk/fk type match. Don't ref an int4 with an int8, etc...