Thread: Varchar or integer primary key?
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. Thanks in advance, Rafael
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...
Rafael Charnovscki <charnovs@unesc.rct-sc.br> writes:
> 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)?
You don't *have to* use an integer column, but I would expect it to be
more compact and faster than the varchar alternative. How much faster
is hard to say without a lot more information about what you'll be doing
with it. You might try setting up a test database and experimenting.
The only really serious gotcha in this area is to make sure foreign key
columns are declared with exactly the same datatype as the referenced PK
column. Postgres will generally let you get away with using slightly
different types (for instance, int versus bigint or char versus varchar)
but you'll pay dearly in performance.
regards, tom lane
Thanks for the answers. They really helped me. As I thougth, that is sort of issue related to modeling, design, RDBMS and experience! Best regards, Rafael Charnovscki > Rafael Charnovscki <charnovs@unesc.rct-sc.br> writes: > >>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)?