Re: Primary key efficiency

Tom Lane wrote:
> 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.

I'd assumed databases would save the number of blanks needed in any
position, and restore/add the blanks when the field was retrieved.

> 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.

I assumed that for some reason it would be faster or more efficient
than something of unfixed length or else why does it exist? Maybe it
is only kept for compatability purposes?

> 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.

That is something hard to decide on. If a part becomes obsolete, i
thought maybe i could fill in an "obsolete" field so that any report
that is generated using it will alert me to change to a new part in
anything that uses it.

I'm using postgresql from php script embedded in html (apache on debian, etc).