Re: Natural keys vs Aritficial Keys

Walter Mitty wrote:
I'm confused about some recent trends in database design.

When I learned databases, about a quarter of a century ago, the preferred
practice was to use natural keys to refer to individual table rows, unless
there's some real good reason to go with an artificial key. I've run into a
few cases where I chose to use an artificial key, but most of the time I've
used a natural key. (I should clarify: if a university's registration
office assigns each student a StudentID, before entering that student into
the database, I'm treating that key as "natural" in the context of the
database, even though one might argue that it's "artificial" in some other
context.) This generally surfaced in the choice of one or more columns to
be declared as a PRIMARY KEY.

Every time the issue of natural keys comes up in Stackoverflow.com, the
prevailing view seems to be that the best primary keys are artificial and
opaque. And responses that take this view get the votes. They emphasize
efficiency (mainly efficiency in the index that you usually get
automatically when you declare a PRIMARY KEY). But data integrity seems to
be forgotten, here. The same is not generally tru when the conversation
turns to referential integrity. Slowly but surely the programming community
seems to heve been dragged, kicking and screaming, into turning over
referential integrity enforcement to the DBMS.


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.

Is data integrity less important than it used to be? Are most people
building databases that get embedded in some appplication to be sold to the
non technical buyer? Is something else going on that I'm unaware of? Can
some body explain to me what these people are thinking?


I don't think it's you who's confused.
.

Relevant Pages

  • Re: Oracle NULL vs revisited
    ... Frank van Bortel wrote: ... for purposes of data integrity with most data. ... Then why say this "Natural keys can not change. ...
    (comp.databases.oracle.server)
  • Re: Why use a composite PK ever?
    ... In the databases of the Norwegian national health insurance, ... So after such cases I'm very sceptical about natural keys as primary ... Leif Biberg Kristensen | Registered Linux User #338009 ...
    (comp.databases)
  • Re: Natural keys vs Aritficial Keys
    ... When I learned databases, about a quarter of a century ago, the ... Somebody mentioned in an answer on StackOverflow that Rails ... database design would adopt that practice, and think of it as a de facto ... So people who argue for modeling with natural keys, as I do, are out of step ...
    (comp.databases.theory)
  • Join Tables
    ... Sorry this is a repost ... I have 3 tables from 3 different databases they are exactly the same in ... I have given them each their own name for data integrity ... but I have to create a duplicate query for each location if I am ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Check integrity data in distributed database
    ... What do you mean with data integrity? ... Do you mean to check if two databases ... Andrés ... > Please tell me the way to check data integrity between server1 and ...
    (microsoft.public.sqlserver.server)
Loading