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: Brian Selzer <>
Date: Fri, 28 Jul 2006 16:01:09 GMT
Message-ID: <9xqyg.12985$>

"Anith Sen" <> wrote in message news:ead3t6$d34$
> Keith,
> 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.

I used the person's name to simplify the discussion. I could have used a part number, a supplier number, or a compound key. The point is, if the value of one or more key attributes can change from one database state to another, then they will. I've seen this happen many times in the decades I've been at this. Assuming that it won't happen is a sure-fire way to get burned. Using only the operations available in the Relational Model, it is not possible to correlate the facts in one database state with those in the next. Consequently, transition constraints cannot be enforced--at least not without employing some implementation-specific extension. Oracle, DB2 and SQLBase have FOR EACH ROW triggers that can be used to meet that need. SQL Server doesn't, so surrogates are the only way to do it--at least reliably.

>>> > 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.

If you can't correlate the facts in one database state to those in the next, then it is not possible to define a constraint that depends upon such correlation, such as a transition constraint.

>>> > Should the proposed state be rejected? Or is it possible
>> that Jane...
> [brevity]
> 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.

The dbms need not understand the external predicate, it only needs to be able to correlate the facts in one database state with those in the next. In other words, it only needs to know what was already known, and how that knowledge will be changed by an update. Again, this is not possible if it is not guaranteed that the value of a key in one database state will not change in the next.

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

Actually, yes.

> --
> Anith
Received on Fri Jul 28 2006 - 11:01:09 CDT

Original text of this message