Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one)) - Mailing list pgsql-novice

From Antonios Christofides
Subject Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))
Date
Msg-id 20031104185125.GA5115@localhost
Whole thread Raw
In response to Almost relational PostgreSQL (was: one-to-one)  (Michael Glaesmann <grzm@myrealbox.com>)
Responses Re: Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))
List 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",
"image/jpeg").

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.

pgsql-novice by date:

Previous
From: "D. Spoon"
Date:
Subject: Re: OT: OS/distribution recommendations
Next
From: kg@kgb.une.edu.au (Klint Gore)
Date:
Subject: test if update statement did anything in a transaction?