Re: [NOW ANSWER Aaron's QUESTION CELKO]

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Thu, 27 Aug 2009 18:57:41 -0700 (PDT), --CELKO-- wrote:

 I guess it's too much to ask you to directly admit that the real world has a place for surrogate keys.  <<

True surrogates are fine; you will never see them.

If I never see them, they are not true surrogates. At least not the
surrogate keys Dr. Codd was refering to when he wrote: "..Database users
may cause the system to generate or delete a surrogate, but they have no
control over its value, nor is its value ever displayed to them ..."(Dr.
Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending the
database relational model to capture more meaning. ACM Transactions on
Database Systems, 4(4). pp. 397-434.

Database users do not get to see the value. I am not a database user, I
am a developer and a DBA. I do get to see the value. If I didn't see it,
it would not be part of the logical model and there would be no need for
Dr. Codd to discuss it - in fact, he would be unable to discuss it
without violating three of his own rules: rule 1 (information rule),
rule 2 (guaranteed access rule) and rule 8 (physical data independence).

Codd was clearly refering to the concept of introducing an extra column
that holds a computer-generated key which can act as a surrogate key for
the candidate keys dictated by the business requirements.

Today, I was
looking at a table with 38+ Million rows of street addresses. It was
ported from SQL Server 2000 to Oracle and the only key is a SEQUENCE
that was translated from an IDENTITY in the original.

Bad use of a feature doesn't make a feature bad. You constantly come up
with such examples to underpin your hatred of IDENTITY. But I'm sure
you've seen queries with overuse of subqueries, causing horrible
performance and an untangable mess of query "logic" (and I use that word
in a very loose sense) - and yet, you still are not warmongering against
subqueries.

Why can you not admit that, when PROPERLY used, the IDENTITY attribute
is an acceptable (though probably not the best) way to fill a gap that
was left in SQL-92?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages

  • Re: Guidelines to a decent support of surrogate key implementation
    ... totally unrelated to the logical data model. ... A surrogate key is system generated to replace the actual key ... with a quote from Dr. Codd: ... Extending the database relational model to capture more meaning. ...
    (comp.databases.theory)
  • Re: Set Based Row Duplication
    ... Surrogate Keys are keys in the usual Relational sense but have the following specific properties: ... Their values serve solely as surrogate for the entities they stand for ...... ... When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted. ... A quote from Dr. Codd: "..Database users may cause the system to ...
    (microsoft.public.sqlserver.programming)
  • Re: Negative Numbers in "Identity" or" Autonumber" fields
    ... WHAT IS A SURROGATE KEY? ... A quote from Dr. Codd: "..Database users may cause the system to ... "There are three difficulties in employing user-controlled keys as ...
    (comp.databases.theory)
  • Re: Set Based Row Duplication
    ... WHAT IS A SURROGATE KEY? ... A quote from Dr. Codd: "..Database users may cause the system to ... "There are three difficulties in employing user-controlled keys as ...
    (microsoft.public.sqlserver.programming)
  • Re: Database design, Keys and some other things
    ... Dr. Codd: "..Database users may cause the system to generate or delete ... This means that a surrogate ought to act like an index; ... "There are three difficulties in employing user-controlled keys as ...
    (comp.databases.theory)