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

Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))

From: Antonios Christofides <anthony(at)itia(dot)ntua(dot)gr>
To: pgsql-novice(at)postgresql(dot)org
Subject: Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))
Date: 2003-11-04 18:51:25
Message-ID: 20031104185125.GA5115@localhost (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Josh Berkus wrote:
>  Another tip:  beware of over-reliance on surrogate autoincrement
>  keys.  They are convenient and necessary some of the time, but use
>  real keys where you can.    The current industry reliance on "ID"
>  primary keys encourages sloppy thinking .... and sloppy schema ... by
>  DBAs.  I have caught myself building tables without any real keys
>  this way.

Speaking of this, I'm finishing the design of a database and I tried to
follow this rule, but there were very few cases where I could. For
example, I have a table with device types, the key to which could be the
composite (manufacturer [a numeric id], modelname [a string]). I didn't
want to use that, however, because the user might type the wrong
modelname and later want to correct it. Not to mention that I've
frequently seen a different model name on the cover and on the label of
some machines.

I decided to use natural primary keys only for four entity types (I have
21 in total): languages, countries, states of the US, and
mime types (examples of keys are, respectively, "en", "UK", "CA",

You think that 4 out of 21 is a good score? Should I allow primary key
changes?  Since the RDBMS does not support cascade updates, I'd need to
write triggers to do the job, and I would also have to make the
constraints deferred. And I doubt it would apply to more than one or two
entity types.

In response to


pgsql-novice by date

Next:From: Antonios ChristofidesDate: 2003-11-04 18:52:23
Subject: Re: Almost relational PostgreSQL (was: one-to-one)
Previous:From: D. SpoonDate: 2003-11-04 18:34:38
Subject: Re: OT: OS/distribution recommendations

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