From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Glaesemann <grzm@myrealbox.com>
Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 23 Jul 2004 10:07:48 -0400
Message-ID: <19813.1090591668@sss.pgh.pa.us> (raw)
In-Reply-To: <FF7F62AC-DC8E-11D8-AA48-000A95C88220@myrealbox.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
<200407221005.59482.josh@agliodbs.com>
<20040723085721.C728@hermes.hilbert.loc>
<0407231327461F.01150@thenilgiris.com>
<FF7F62AC-DC8E-11D8-AA48-000A95C88220@myrealbox.com>
Michael Glaesemann <grzm@myrealbox.com> writes:
> You appear to be misunderstanding the purpose of a primary key. A
> primary key is used to ensure there is a way to identify each row
> uniquely. It is quite independent of which columns you may or may not
> want to search on. If name is not going to be necessarily unique in the
> table, it isn't a primary key.
The other standard reason for using a made-up value as primary key is
that it's under your control and you can guarantee it isn't going to
change: one record will have the same primary key for its entire life,
which vastly simplifies handling of foreign keys and so forth.
If you use any real-world datum as primary key you necessarily buy into
tracking changes in that real-world value. And handling duplicates.
Names are certainly not unique, and they do change (marriage, etc) and
people do miskey them and then expect to be able to fix the error later.
As Achilleus' nearby story shows, you can have these problems (certainly
the misentry part) even with imported data that is allegedly someone
else's primary key; part numbers, USA social-security numbers, etc.
regards, tom lane
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: tgl@sss.pgh.pa.us, grzm@myrealbox.com, lawgon@thenilgiris.com, Karsten.Hilbert@gmx.net
Subject: Re: surrogate key or not?
In-Reply-To: <19813.1090591668@sss.pgh.pa.us>
* 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