Poof... your wish is my command. I have no wish to be provocative in my selection of titles, nor in the tone of my posts. (Thank you for the guidance.) I changed the thread-title, although the child-records (existing replies) were not changed thereby.
Nodding politely (but, flinching slightly) at your use of the word, “abhor,” I find it to very often be the case that no “human provided” number serves well as a primary-key. As the subject of a “UNIQUE-indexed column,” perhaps, but not as “the glue that sticks everything together.”
I say this because “unique numbers” in the human world are often not perfectly unique, or if they are, do not stay that way. And when (not if...) one of these eventualities happen, they are very problematic for the computer. An account-number change, for instance, that “ripples” through thousands of records, for instance, and/or that invalidates an archive if the account is of very long standing. An assigned number (in-use by a human) that cannot be stored. Or, as I related above, a number that does not have the one-to-one correspondence with reality that it should (according to the good Dr. Codd) have. In those situations, surrogate (or “synthetic”) keys might excel. You are obliged to handle, and to continue to handle, a real-world circumstance that is “mathematically speaking, compromised.” The business isn’t going to stop for a schema. I dealt with that first-hand, and yes, it shaped my thoughts.
Of course it is a choice to be made; not an absolute maxim. “So help me, Codd.”
| |