Re: Primary key efficiency

Russell Shaw <rjshaw(at)iprimus(dot)com(dot)au> writes:
> Is a long field ok for a primary key, or should i use
> a unique integer?:

> create table parts (
> manufacturers_code char(40) primary key,
> description char(40),
> man_id int references manufacturers (man_id)
> );

There's nothing wrong with using a character field as primary key,
but I'd advise you to think twice about defining it as char(40).
Almost certainly you want varchar(40), so as not to be wasting huge
amounts of space on padding blanks. For that matter, where did
the "40" come from in the first place? Is there a really good
application-driven reason to limit the codes or descriptions to 40
characters? If your answer reveals that the number was picked out
of the air, I'd suggest dropping the constraint entirely. Text or
unconstrained varchar is a better choice, though it's not completely
SQL-standard.

One thing you should think carefully about before using externally
supplied data as a primary key is "what happens if the manufacturer's
code changes"? You'll have to update not only this table, but all
references to it from other tables. It's usually considered good
practice to choose primary keys that will *never* change, and that
essentially means that they can't have any externally-imposed meaning.
The popularity of auto-generated serial numbers as primary keys comes
from this consideration.

regards, tom lane