Skip site navigation (1) Skip section navigation (2)

Re: surrogate key or not?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: Kenneth Gonsalves <lawgon(at)thenilgiris(dot)com>,Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>, pgsql-sql(at)postgresql(dot)org
Subject: Re: surrogate key or not?
Date: 2004-07-23 14:07:48
Message-ID: 19813.1090591668@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Michael Glaesemann <grzm(at)myrealbox(dot)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

In response to

Responses

pgsql-sql by date

Next:From: Josh BerkusDate: 2004-07-23 17:17:11
Subject: Re: surrogate key or not?
Previous:From: Janning VygenDate: 2004-07-23 13:59:38
Subject: Re: surrogate key or not?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group