Re: Natural keys vs Aritficial Keys

From: Roy Hann <specially_at_processed.almost.meat>
Date: Sat, 16 May 2009 03:58:32 -0500
Message-ID: <>

Walter Mitty wrote:

> I'm confused about some recent trends in database design.

No you're not.


> What I don't get is whether I've understood something that today's
> trendsetters never learned or whether they are taking into consideration
> matters that I never considered important, back in the day. I've read their
> arguments carefully, and it seems to me that failure to declare UNIQUE and
> NOT NULL constraints on the most widely used candidate keys is simply an
> invitation to degradation of data integrity. PRIMARY KEY is a convenient
> way to get those two constraints with one declaration.

That misses the point. PRIMARY KEY and UNIQUE are supposed to prevent potentially contradictory facts. They have nothing to do with addressability. If RDBMSs or SQL DBMSs needed the concept of an address they would have it and it would be unique and you wouldn't have to everywhere repeatedly assert the most obvious property of an address is uniqueness.

I think the urge to want to address rows is not just a hankering for network databases though. Programmers worry about what to do if a user mis-keys a natural key value which then gets propagated to a number of tables. If they have kept the natural key in one place it needs to be fixed in only one place. I've had quite good luck weening colleagues off spurious synthetic keys by encouraging them to declare their foreign keys with ON UPDATE CASCADE. (One or two demand benchmarks before they believe it is practically free, but generally they get it.) I suspect that it became established "best practice" to use synthetic keys everywhere back when few SQL DBMSs supported ON UPDATE CASCADE and it has just stayed with us.

> Can some body explain to me what these people are thinking?

They aren't.

Received on Sat May 16 2009 - 10:58:32 CEST

Original text of this message