Re: Surrogate Keys: an Implementation Issue

Roy,

Thanks for the considered response.

I'm not sure I made myself clear, or else I misunderstand some of your
points:

Yes. It creates a comforting delusion about what you know about the real
world, as well as creating lots of extra work, and concealing correct
information behind spurious indirection.

and:

the change of value everywhere. But it also costs a lot of coding, testing,
development discipline, and obscurity.

How would this be extra work? From the users, and by user I mean
programmer, there is no surrogate key. This is purely an
implementation issue. I'm talking about the internals of the DMBS, not
desiging a DB. The DBMS does not ever expose the surrogate key. Any
from your perspective, as someone designing a DB it would appear no
different than any other DBMS. You would have no knowledge that your
primary key was not infact propagated all over the place.

If someone made the sweeping recommendation that my "table should have a
system-supplied, auto-incremented, and hidden, 'super primary key'." One
might as well use pointers otherwise--they'd do the same job, and probably a
heck of a lot faster.

Again, as a programmer, or DBA, should you really care (unless its
slow, of course) if it is an implementation issue?

There is, I think, actually a two-pronged benetit to the surrogate key
as a behind-the scenes immutable identifier. The first one is, as you
say, perhaps not so consequential: its easy to change the natural
primary key, and it saves space.

The second is perhaps more profound. Consider a rollback database, or a
database that must provide a complete audit trail of every change. For
example, the database must provide the answer to "who changed this SS
number from X to Y, and when did they change it? As far as I can tell,
if there is no way to answer this without an immutable identifier. ( I
suppose you could design the DB to handle specific cases, but I'm
interested in DBMS with native rollback and audit trail support.)

Again, let me restate that I'm talking about the internals of the DBMS,
not how to design a database.

.

Relevant Pages

  • Re: candidate keys in abstract parent relations
    ... strongly that a lot of the time the database designer does not consider the ... With the primary key change, I was more thinking of all the locking and page ... Whether they send back a small surrogate key or an entire ... I'd say the DBMS would be doing ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... scalable mechanism for the generation of surrogate key values. ... has no place in a relational database, ... include a constraint that will ensure uniqueness of the real key. ... result in a constraint violation, and no rows inserted into the table. ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... > scalable mechanism for the generation of surrogate key values. ... > has no place in a relational database, ... > include a constraint that will ensure uniqueness of the real key. ... > result in a constraint violation, and no rows inserted into the table. ...
    (comp.databases.theory)
  • Re: candidate keys in abstract parent relations
    ... > interface into the applications using the database. ... > which item in the drop down is selected, if we hadn't used a surrogate key ... > from a business stand point is there to store data. ... business data into a database until the argument for doing so becomes ...
    (comp.databases.theory)