Re: surrogate key or not?

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