From: Janning Vygen <vygen@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 23 Jul 2004 15:59:38 +0200
User-Agent: KMail/1.6.2
Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<E7829CAA-DC99-11D8-AA48-000A95C88220@myrealbox.com>
	<0407231532531I.01150@thenilgiris.com>
In-Reply-To: <0407231532531I.01150@thenilgiris.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200407231559.38069.vygen@gmx.de>

Am Freitag, 23. Juli 2004 12:02 schrieb Kenneth Gonsalves:
> ok, i'll rephrase the whole thing:
>
> i have a master table with two fields:
> id serial unique
> name varchar not null (and will be unique)
>
> i always make 'name' the primary key, and since it is the primary key, i
> dont explicitly specify it as unique, and after postgres 7.3 came out, i
> have added the 'unique' constraint to the 'id'
>
> on looking at the gnumed schema, i saw that although 'name' was unique, the
> serial key, 'id' was made the primary key. So i wondered why and whether
> there were advantages in doing it this way.

Does your question relates to surrogate vs natural keys discussion?

I made some researches a few months ago and read a lot including:
http://www.intelligententerprise.com/print_article_flat.jhtml?article=/030320/605celko1_1.jhtml
http://www.winnetmag.com/Articles/Print.cfm?ArticleID=5113
http://www.dbpd.com/vault/9805xtra.htm
http://www.sapior.com/Resources/Surrogate_Keys/surrogate_keys.html
http://www.bits-pilani.ac.in/dlp-home/online/studymat/sszg515/lecture3.htm
http://www.bcarter.com/intsurr1.htm

i decided to use natural keys wherever possible and i have many primary keys 
spanning up to 4 attributes. And it works really fine. Performance is great, 
the schema is easy to use and i am so glad to use the natural key approach.

Writing SQL queries and php code is much easier!

By now i try to avoid surrogate keys (like with SERIALs datatype) wherever 
possible. Most articles advocate surrogate keys and at first it looks like an 
advantage in a web environment because selecting and transmitting a 
multi-column primary key in a form field ist very difficult.

Imagine a <select> element, but you have only one value to be returned. My 
trick here is to have the primary keys used in the select element saved in a 
session array and using the session array index as a select element value.

But the strongest argument for me is: All candidate keys have to be unique 
anyway. And postgresql builds an index anyway for every UNIQUE key, because 
thats the way postgresql checks uniqueness. So why add another artifical key 
with another index when you can use the one which is given anyway.

Think of usergroups identified by name and members which are identified by 
user groups name and email adress, then  you've got the pseudo schema 

create table usergroups (
   ug_name text,
   CONSTRAINT uq_ug UNIQUE (ug_name)
);

create table members (
   ug_name text,
   mb_email text,
   CONSTRAINT uq_mb UNIQUE (ug_name, mb_email),
   CONSTRAINT fk_ug_name FOREIGN KEY ug_name REFERENCES usergoups (ug_name)
);

so you have to indexes uq_mb and uq_ug anyway. So why dont use them as Primary 
Keys?? With two more attribute for a surrogate key like

   ug_id SERIAL PRIMARY KEY

in table usergroups and

   mb_id SERIAL PRIMARY KEY

you have additional 4 bytes to store for each table row and one more index for 
each table.

So my conclusion is: i dont see any benefit in using surrogate keys. But this 
must be wrong because so many people are using and advocating surrogate keys. 
They might only be useful in circumstances where no natural key is given.

kind regards,
janning