>>Either way, these "intelligent" keys are not guaranteed to be unique, at least with the examples you have given.

What a debate...< s >... Seems like this one got trotted out last year or so...

The issue is simple...And FWIW Chris, you have been spot-on in this discussion.

The first thing to distinquish is a Primary Key, from a Key, from a Candidate Key.

PK's do two things:

1. They provide the link between related records.
2. They are the key to looking up specific records.

They should surrogate keys (i.e., not meaningful). Integer/auto-incrementing is the way to go. In SQL Server, they are called identity fields. The user should NEVER have control over these items. In fact, a user should NEVER see them.

Regular Keys - no big deal here. They provide alternate ways to lookup data in order to enhance performance...

Candidate Keys - This is where I think Walter munges concepts. Based on this discussion and past ones, Walter likes to take what would otherwise be a candidate key and instead, use them for Primary Keys. The only think candidate keys and primary keys have in common is the uniqueness factor. Do you want the db engine to enforce uniqueness. There are times when values should be unique in a table that have nothing to do with RI. Here is a good example - an employee ID badge number. It needs to be unique - yet it has nothing to do with RI. Of course this would be a key field. Whether you want to make it a candidate key and have the engine enforce uniqueness or have it as a regular key and enforce uniqueness via a stored proc, trigger, biz rule, etc - is a matter of preference. i.e., there is no one right way to do this.

That is it in a nutshell. It can't get simpler than that....but it sure can be over-complicated by some of the rocket-scientists in this thread. Present company excluded...< bg >....

< jvp >