Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: identity columns

Re: identity columns

From: tj bandrowsky <>
Date: 12 Feb 2002 17:35:32 -0800
Message-ID: <>

> >>> If you insist on a true natural key then get your employees to
> >specify the place and time of their birth. Getting the time to a
> >precision of 1 second and the latitude, longitude and altitude to a
> >precision of ~1ft should guarantee
> >uniqueness. <<

I think this makes the point that the entire notion of natural keys is absurd. You mentioned using position, but there is no concept of absolute position for anything, we all exist in relative terms. That's Einstein for ya, and it's human nature too.

Humans define everything in relative terms, our vocabulary and very means of learning seek to categorize things based upon their relative position within the overall space of all things knowable. To say that there is an absolute identifier of something is utterly impossible.

Talk about something? Talk about cars? Which car? A toyota? A ford?  Which is your favorite? the blue ford, the red ford? or the toyota?  the camry? or the thunderbird? the camry with the cd player or the ford with the cooler body? cooler than what? the camry?

There is no natural key. It all depends on what I'm talking about, what I'm doing, what my business is doing, what the needs are. All information is relative.

For example, say I have to make a database on the shelf. On the shelf I have a red ball, a blue ball, and a green ball.

Now, you or I could understand the context of the situation and walk into a room and say: "hand me the green.", or just "green", and know via the context of our conversation, what the object we are discussing is.

Databases do not know context. We have system management tools that try and transform a relative world into absolute terms. No wonder it costs so much money for a big company to just find out how many widgets it sold. what kind of widget? the red one, the red one I made last year? the red one that I sold for 10 cents off during christmas or the ones that I gave away when someone bought two blue ones during the superbowl...

So I make a table called balls and I set the primary key to be the color - red, green, or blue. Someone comes along and adds another green ball to the shelf. In all respects this new green ball is identical to the first ball, yet, there are four of them. How do I uniquely identify which ball?

If it doesn't matter, then my database could not drive a robot arm to grab me a single ball. Which green one? If my robot arm were Windows based, that means GPF... technote.. Microsoft Robot Arm cannot do this or that, so it crashes, this feature is by design. So, it does matter. Maybe I could use the date that I added the ball to the shelf. Maybe I could use the position of the ball.

Whatever I come up with for a "natural" primary key, I'm ultimately selecting a single characteristic based upon the whole context on behalf of the users of my database, and one of those users is bound to be forced by circumstance to change the context I am working with and bust my convention for uniqueness.

Any "natural key" I select, I'm making up, just as much as a guy who uses one, two, three, or four. Neither I nor the guy choosing a sequence is safe - he's got to contend with some other kind of thing that somebody else might need to label as "one", and I've got to contend with some other object being labelled as "ball closer" or "ball farther". In both cases, the uniqueness of scheme is wrecked.

All keys are a synthetic imposition of the database designer's understanding of the context in which objects must be unique. Natural or synthetic, your guess is as good as mine. Maybe someday we will have systems that painlessly let context be added to new keys to differentiate them, rather than having to go destroy old ones.

I posit that the only natural key of a single object is the entire object itself within its context. Because the context contains some duplication, the entire key is not valid 1NF, and therefor, is not a key. Natural keys are thus a contradiction in terms.

Perhaps we need to let go of the idea of objects being discrete, and rather come up with systems that let objects be defined by the context they are used in... Received on Tue Feb 12 2002 - 19:35:32 CST

Original text of this message