Re: Surrogate Keys: an Implementation Issue

paul c wrote:

> I noticed this in your reply to Bob Badour: "So for most tables it is
> not exposed, but for some it is." I'd say all bets are now off, doesn't
> this put us back where we started, ie. a system-supplied candidate key?

As I said before, it does double duty. If the user requests a auto-incremented surrogate key, it is exposed. If he does not, then it is not exposed. In either case it is used by the DBMS behind the scenes to uniquely identify a row.

> (BTW, as far as "track a row over time" goes, I'd say using real
> time-stamps has fewer problems than auto-increment values for which it
> can be quite hard to avoid duplicates if the incrementing is done by
> user code and the engine multi-tasks

Well, clearly, the DBMS must do the generating of auto-incremented values. But I'm not sure we are talking about the same thing here. When I say "track a row over time", I'm talking about being able to, for example, report on the fact that a row went through the following history:

Name (pk) City

=====         ======
Palu            New York          // Lastweek
Palu            New Jersey       // yesterday
Paul            New Jersey      //  today

Since my primary key has changed, I need some immutable identifier so I know these are in fact part of the same set.

Not sure how a timestamp helps here. Received on Thu Jul 20 2006 - 12:21:43 CDT