To: Michael Glaesemann <grzm@myrealbox.com>
Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>,
	Karsten Hilbert <Karsten.Hilbert@gmx.net>, pgsql-sql@postgresql.org
Subject: Re: surrogate key or not? 
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>
Comments: In-reply-to Michael Glaesemann <grzm@myrealbox.com>
	message dated "Fri, 23 Jul 2004 18:59:33 +0900"
Date: Fri, 23 Jul 2004 10:07:48 -0400
Message-ID: <19813.1090591668@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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