From: Aaron Bono <postgresql@aranya.com>
To: David Clarke <pigwin32@gmail.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: operationsengineer1@yahoo.com <operationsengineer1@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Alternative to serial primary key
Date: Fri, 7 Jul 2006 03:32:33 -0500
Message-ID: <bf05e51c0607070132u1a1e4f3cy7ac7176a62e239b9@mail.gmail.com> (raw)
In-Reply-To: <12b7ac1e0607070107t78a808aeuc8aba982d593abff@mail.gmail.com>
References: <bf05e51c0607060902vdd8d993u470c128abf127d3d@mail.gmail.com>
<20060706183920.46008.qmail@web33314.mail.mud.yahoo.com>
<12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com>
<bf05e51c0607061443t2eb0cbbfl569b7b25c74166d7@mail.gmail.com>
<1152222607.13851.280.camel@state.g2switchworks.com>
<12b7ac1e0607070107t78a808aeuc8aba982d593abff@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, pigwin32@gmail.com, smarlowe@g2switchworks.com, operationsengineer1@yahoo.com
Subject: Re: Alternative to serial primary key
In-Reply-To: <bf05e51c0607070132u1a1e4f3cy7ac7176a62e239b9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox