In considering Michelle's opinion in the identity vs. meaningful value debate, I think it's important to remember that Michelle wrote this in 1999, before SQL Server finally got around to supporting DRI (Declarative Referential Integrity) which allows for automatic Cascading Updates and Deletes. As I understand it, all enforcement of Referential Integrity was left up to the database designer to handle them through triggers. If I had to write a trigger just to enforce the integrity of a foreign key relationship, I'd most likely be picking keys that would not need to change too!

My official, bottom-line, opinion of which primary key method to ALWAYS use is, "Use whatever works best in each situation".

DAN9/22/2006 10:06:31 PM

"Now, to be accurate, the old customer 9006CODEN010 should be 9006CODIA010. "

No, check your baggage tags!

As much as one might wish it to be DIA, which how most people refer to the airport facilities, it is still DEN.

Jenn7/10/2006 7:59:23 AM

"Consider another simple example - names are attached (artificially) to new-born children, usually by the family head and are generally (in most cases) unique within the family."
two words - George Foreman (he named all his sons "George")

Anonymous User 1/22/2005 8:02:55 PM

I want to refer to the next piece in the article:
"Nonidentifying Value
A common mistake among database designers is trying to build intelligence, or meaning, in to the primary key."

I then want to refer you to a document written by Shajan Miah where he deals with the Identity problem and I quote:
"Most of the published literature on database systems, when dealing with some of the problems associated with the use of PKs, only adopts a narrow symbol level perspective, i.e. difficulty in maintaining updates, changes, and etc. Very little, if any, consideration is given to the sometimes important role of the PK at the knowledge level (Newell 82, 93).
The most important function of the PK is as an interaction element between the real-world and the database. It is thorough the primary key that we usually query the database. The primary key is of fundamental importance if we are to "usefully" relate the concepts of the database to the real world.
Here we are going beyond the symbol level (S/L) to the knowledge level (K/L) use of the primary key concept. "An object retains its identity through arbitrary changes to its own state. This is in contrast to the relational model where the properties of an entity must be sufficient for it to distinguish it from all other entities. That is, there must be an attribute or attributes whose values uniquely and immutably identify that entity. The properties of uniqueness and immutability are not always present in the real-world and it is often necessary in relational databases to introduce artificial identifiers for entities."(Hughes, 1993, p.117) (This appears to be a paradoxical statement, as the author also admits that sometimes unique values may not exist!) Hughes, implies in the above statement that this is a limitation of the Relational model, i.e. the introduction of an artificial value to identify some real-world concept. According to Hughes, the OO model does not have this problem as it uses system created OIDs (a bit level concept), some programming pointers to identify the real-world concept.
However what Hughes does not consider is that, we do this in everyday life in the real-world we create artificial keys to identify real-world objects. For example the UK registration scheme on cars, the Ministry of Transport deliberately employs an artificial mechanism to identify the cars in the real world (in the UK). This is not done for efficiency purposes (bit & symbol level), but rather for identifying the item (in this case a car) in the real world, and hence is at the knowledge level. Consider another simple example - names are attached (artificially) to new-born children, usually by the family head and are generally (in most cases) unique within the family. These values identify a person within the context of the family. The deliberate creation of keys, also has another importance, from a HCI (Human-Computer Interaction) perspective, in that control of key values is retained by the user.

Date(1995), also notes two similar reasons why PKs are needed.
1. The concept of uniqueness does not apply to some objects (that is unless one goes to the extreme of including details of the physical matter of the object). This as we showed earlier, is applicable to the UK motor industry, where registration number is created to differentiate between cars with similar properties.
2. Standard way of linking the object to the real-world - the control is with the user, in contrast to the OO model, where the system decides. The authors of the Third-Generation Database Manifesto (1990), also argue that "An immutable primary key has an extra advantage over a system-assigned unique identifier because it has natural, human readable meaning. Consequently, in data interchange or debugging this may be an advantage."(p.12).
Although, the OID approach to identification used in the OOPar, does overcome some of the symbol level problems associated with PKs, it ignores the sometimes important role of the PK at the K/L and therefore can result in fundamental problems at the K/L. Our claim is not that the OO model cannot cope with the PK concept, but rather that it takes a narrow bit & symbol level perspective, and as a result the concept of PKs is not actively supported by the OO community. Thus, although the OO model claims to eliminate the need for keys (Hughes, 1993) the preceding examples illustrate the importance of the PK concept at the K/L."
http://members.aol.com/shaz7862/object/critique.txt

Jeff Deacon 4/25/2001 1:20:27 AM