Re: Natural keys vs Aritficial Keys

From: Walter Mitty <>
Date: Sat, 16 May 2009 18:35:36 GMT
Message-ID: <YHDPl.1490$>

"Roy Hann" <specially_at_processed.almost.meat> wrote in message
> Walter Mitty wrote:
>> I'm confused about some recent trends in database design.
> No you're not.
> [snip]
>> 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.
> --
> Roy

Thanks for your response. You are another regular whose responses often provide me with valuable insight.

Based on the discussion in stackoverflow, I can neither confirm nor deny your experience with CASCADE bringing people around. If you say that's been your experience, then I'll believe you.

What I will say in response kind of generalizes your comment. Some design decisions involve a trade off between ease of development now and ease of data management later on. In that trade off, ease of development seems to win every time. I can't prove this, but I think a lot of those developers imagine a DBA to just a database baysitter, someone who makes sure the database isbacked up, prevents hackers from storming the citadel, and does little else. From my expeience in the 80s and 90s, that was only a small part of the ways DBAs added value to data.

Thanks again. Received on Sat May 16 2009 - 20:35:36 CEST

Original text of this message