Re: Surrogate Key vs Production Key

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0410131031.49b21ed_at_posting.google.com>...

> >
> > I don't understand. Are you suggesting that surrogate keys
> > are buggy or that only surrogate key systems have bugs?
>
> If you have a bug and it corrupts values in the child table, you may
> have no way to restore those child records back to the proper parent.

That is complete bollocks. First of all: corruption of values is corrected with data recovery. It has NOTHING to do with modelling, PKs, FKS, keys, whatever!

> Why because that is not relationship between the Surrogate key value
> and the data it is supposed to connect. It is often just a value

This is where your examples and arguments are completely wrong. If you do not establish a referential integrity constraint between ANY two PK and FK keys (natural or not is COMPLETELY immaterial), then you WILL have the potential for wrong data.

It has NOTHING to do with how those keys are generated/populated. The problem is NOT the use of surrogate or natural keys, the problem is the ABSENCE of a RI constraint!

Can we for once stop invoking absolutely farcical and irrelevant scenarios that have NOTHING to do with use of surrogate versus natural keys? Since WHEN does the use of surrogate keys preclude the use of RI? Helloooooo?

> selected from a sequence. So changing the FK value from 1000 to 10001
> just changed the parent from john smith to abby hutchison. But a bug
> in a natural key which changed john smith to john smithe might be
> correctable.

No it most definitely is NOT correctable with natural keys. Or you have never heard of how many people there are with the name john smith!

> You made the ZERO claim for surrogate keys, I made no such claim for
> natural keys. My only contention is that it is a lot harder to recover
comp.databases.oracle.server> from a problem when the surrogate key values get screwed up.

The "surrogate key values" do NOT get "screwed up"! THAT is the whole argument for using them. They are not manipulated by ANYONE other than the code and/or database engine. UNLIKE natural keys, that indeed MUST have check digits or other devices to avoid user corruption! AS WELL AS whatever other devices must be in place to ensure RI is not lost!

> The Surrogate keys are not part of the real data, they are a
> ficticious relaionship. Logically the SK's are just POINTERS. That
> make it logically a network model, IMHO.

No it most definitely does NOT! A network model is not just a collection of rows in tables with numeric links! That shows you don't have the foggiest idea how a network (or hierarchical) database works.

Suffice to say that non-relational databases do NOT allow for ad-hoc relationships between any two entities (tables).

Something that is at the very root of why relational models are used: with relational, you can always create a join between two tables on any two columns, dynamically and without having to specify a-priori the relationship in the database schema. Regardless of what that join may retrieve.

THAT is why relational is so much more flexible than network or hierarchical models and why it uses the flat table mechanism. While the others use a COMPLETELY different mechanism for data storage and retrieval.

In fact, relational is so flexible that it may indeed allow emulation of what a network or hierarchical database does. And dynamically! That is what your argument was intended to show.

But the internal mechanics between the two are COMPLETELY different and have NOTHING to do with "links", hence the argument's total lack of validity.

The use or not of numeric "links" to rows is a physical design detail that has NOTHING to do with relational modelling or relational logic design of a database, whatsoever!

> > Still waiting to hear of one with SKs.
>
> Then you haven't been listening.

Still waiting. Perhaps you could start by re-reading what I said, instead of jumping to imaginary situations that NOBODY proposed or has ever implemented? Received on Wed Oct 20 2004 - 23:43:29 CDT