Re: Identity key?

>> I tend to disagree. The relational model is not really suited for
logical modelling anyway; that's why entity-relationship modelling was invented. <<

The relational model deals with how the logical model is implemented, then the SQL product has to worry about the physical storage and location. But I want to see the logical model in my SQL. I want data element names that follow the NCITS L8 committee rules. Etc.

>> But if you are using it as such then the term PRIMARY KEY is
nonsense, because that describes an implementation aspect. So if your position is that surrogate keys have no place in a logical model, then primary keys should also be avoided. <<

Unh? Dr. Codd would be VERY surprised to find out that his papers had no mention of primary keys in them <g>. I agree with the basic idea of the relational model that a table MUST have a key, or it is not a table. Keys are a logical concept and always have been. I will grant that the SQL PRIMARY KEY clause is a language requirement with certain properites in addition to those of the primary key concept invented by Dr. Codd.

A surrogate key is created by the database system for its own use and it is never exposed to the user. Artifical keys are created by the users and they are exposed. These two terms get confused. I don't care about surrogate keys, any more than I would care about pointer chains, indexes and all the other various access methods SQL products use -- I never see them as a programmer. I would care about them if I were the PHYSICAL database administrator and had to tune them, buy more disk drives, etc.

What you want is a natural key, if one exists and is easy to record. Why? Because you can verify the data in the database against the reality it is supposed to model.

If you cannot get a natural key, then you want to create an artifical key. It should have some properties that let you verify it against reality it is supposed to model. I like check digits because I can verify the syntax of the encoding in my application program. I like standardized encoding systems, like UPC in the United States or EAN in Europe. I can verify those codes with an outside agency.

But the most useless approach is a key that relates the row to a particular physical location in a particular release of one particular database product -- it has nothing to do with the data itself and cannot be verified when the data changes location.

Whenever possible, I would like to avoid superkeys -- a key which contains a proper subset that is itself a key. This leads to normalization problems.

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 08 2001 - 20:14:04 CET