Re: Alternative to serial primary key

On 7/7/06, David Clarke <pigwin32(at)gmail(dot)com> wrote:
>
>
> The question remains regarding the use of a string value as a primary
> key for the table and as a foreign key in other tables. If I use the
> address column as a foreign key in a differrent table will postgres
> physically duplicate the data or will it simply attach the appropriate
> index magically behind the scenes? I agree that the address column is
> fairly small and I've heard of others using the likes of GUIDs as key
> values which are not a lot smaller than I would expect my average
> address to be.

Theoretically using the address as a foreign key WILL duplicate the data. I
don't know if there is some fancy way PostgreSQL optimizes the use of space
for indexed fields or foreign keys though. Might need to get feedback from
someone who has looked at the source code for that and it may depend on the
version you are using. Unless you are looking at a huge number of records
though, the size difference is of little concern - disk space is cheap.

As far as should you use varchar as a foreign key - as someone mentioned
before, you may be treading on religious territory there. It can be done
and is done frequently. I never do it but I really cannot come up with a
compelling argument why not other than it is just one of the standards I
have adopted for my team.

-Aaron