Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Surrogate Keys: an Implementation Issue

Re: Surrogate Keys: an Implementation Issue

From: Anith Sen <>
Date: Fri, 28 Jul 2006 08:32:30 -0500
Message-ID: <ead3t6$d34$>


The source of this discussion is mostly poor key selection. It has nothing to do with surrogates. Many SQL products have value generators like SEQUENCE, IDENTITY columns etc. And the columns using them are generally non-updateable. So folks try to re-invent the concept of surrogate based on such features and tend to draw conclusions to suit the product specifications. That way, many can justify a particular design that uses such features.

>> > Current: Jane Jones Married Proposed: Jane Smith Divorced
> Do Jane Jones and Jane Smith represent different people?
> Or did Jane Jones get Divorced?

To the user who knows Jane in person, they are the same. To the DBMS, the propositions are distinct.

>> Because the only key is
> mutable, there isn't enough information to answer that.

Mutability of an attribute has nothing to do with it. In fact there is no attribute that identifies Jane is declared to the DBMS at all.

>> > Another problem involves temporal constraints. Consider
> the following states for the people table and a transition
> constraint, Single people can't become Divorced:
> Current: Jane Jones Married Proposed: Jane Jones Married
> Current: Jane Smith Single Proposed: Jane Smith Divorced

Since the DBMS cannot identify them as separate propositions, this is a faulty premise to begin with. To the user (external ) it may make sense, but to the DBMS it does not ( internal predicate ).

This is not a case of temporal constraints. "Foundation of databases" has a set of mechanisms how the state changes are addressed by transition/temporal constraints by a variety of mechanisms.

>> > Should the proposed state be rejected? Or is it possible
> that Jane...


The whole paragraph is based on the faulty assumption that the external predicate should be understood by the DBMS even when no identifying attribute is declared to the DBMS.

>> > It should be obvious that surrogates solve these problems.

Actually no.

Received on Fri Jul 28 2006 - 08:32:30 CDT

Original text of this message