>>>>>Just as having int be the PK type everywhere as a standard is a good thing, so is having meaningless keys as a standard. The point to them is to prevent any kind of cascade updates. You have an invoice number on the invoice header. You have a meaningless key on the invoice header. You use that key to join to line items. The invoice number can be changed all you want with no effect on the line items.
>>>>
>>>>Wrong example - any accountant or auditor will frown at changing an invoice number - but the logic here is correct.
>>>
>>>When computerizing a manual system, one where the key is not generated, but entered by hand, it can be necessary to edit it after a mistake by a user.
>>>
>>>>For instance, one would be insane to make a house number part of any important key, as streets may undergo renumbering. Or the passport number example - it is maybe a good PK for the passport, but not for a person, as passports expire. Etc etc. An invisible PK is not changing ever, simply because it's not doing any sidejob, it's just a key.
>>>
>>>Other than that, I agree. A key is just a key and should not be used as meaningful data.
>>
>>huh ? First of all alternative keys are often meaning full. Further your "should" is your opinion and not shared with amongst the fathers of the relational database.
>
>Actually - there was a long discussion involving Celko and he did admit meaningless keys are completely acceptable. The dictionary definition of keys says it can be meaningful, but that's no "rule". However the instant you start cascading the key you are far better off with meaningless. I don't particularly care what the "fathers" of the relational database meant "back then". The inventor of the wheel has no impact on the modern automotive tire.

I agree that when keys are not static and you need to cascade changes throughout tables, you're absolutely better of with static keys, whether they are artificial, intelligent but generated or entered by hand. OTOH, databases have build in mechanisms to deal with that: The cascading update. Aside from the potential performance impact, the problem begins when you did not define the RI rules properly in your database and the cascade is not happening on a certain table. Yes, that can be real fun.

But this topic is not about two types of keys: intelligent / artificial. There are more variations:

- Manually entered, artificial: users keep lists of numbers elsewhere (yep seen it too many times), but do not have any meaning outside of the system.
- Manually entered, intelligent: Like passport number, SSN
- Generated, intelligent: e.g. Invoice number generated from the system, but has meaning outside of the system
- Generated, artificial, but visible. Meaningless key to identify record, but visible to users for reference in case needed (e.g. Navision)
- Generated, artificial, invisible. Only for storing parent - child relationships, invisible at all times

Also a misconception about PKs and AK. Both keys can be used in joins. This is esspcially common when using database from outside. A SSN in your system might be only an AK in your database, but a PK in another. There isn't technically any reason why this is not a valid practise, though of course you'd strive to use PKs only.






- Generated (could be intelligent or artific