On 7/7/06,
David Clarke <
pigwin32@gmail.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