>>This entire thread underlines why many of us are using ONLY surrogate keys,
>>which can be updated invisibly from a table. Any field the user edits is
>>not included in the primary index. If the user is entering a primary key in
>>a multi-user environment, there is ALWAYS going to be a period of time
>>while the key can be duplicated by another user.
>>
>>Barbara (now stepping off her soap-box)
>
>Step back on it, you have a question from the audience. All these months
>I've been wandering what's the big deal on using PKs if they serve no
>purpose, i.e. contain no meaningful data (remember the dispute on
>'natural keys' two months ago). Now I've tried some ODBC on my machine
>(exporting some log tables into Excel) and I've discovered, much to my
>surprise, that SQL updates require some PK or they scream out bloody
>murder if you don't supply it. OK, it's a SQL requirement and a bit of
>system overhead, but the deletion mark is such a thing and we tolerate
>it for years. OK.
>
>But I've always had a primary key, though I've always controlled it
>programmatically (in single user or file server architectures) - it was
>various user ids, customer ids, item ids etc. Most of the time ordinal
>numbers didn't do - there were lots of legacy coding systems, so we just
>checked for existence of a new (user-supplied) key, and shown the record
>using that key. So, inventing a key just before saving the data couldn't
>work - the key had to be the first field on the form, and it had to be
>checked for uniqueness.
>
>So now I have a double problem - inventing a (surrogate) primary key,
>which can be automatized, and the old problem once more - avoid the
>clash between possible natural unique keys. Since the key is entered as
>a first field, and it does take some time 'till the user gets to the
>last and saves, other user may have gotten the same key from the key
>offering routine; the third and fourth got another ones. Now the first
>three users revert and don't save - I have three unused keys. I don't
>mind, but tax collectors are very suspicious if the invoice numbers are
>not exactly consecutive.
>
>Did anyone solve this problem already?

Here's another thing they never talk about in chapter 1: when is a surrogate key no longer a surrogate key? If a number generated by your program gets used as an invoice number and is seen by end-users, it has arguably become a meaningful key and is no longer a surrogate. If they have to be consecutive, meaning of a sort has already been imposed. One of Robert Keith's threads discussed the question of how to reuse key values. The solution that was supplied, which I don't remember, may not have kept all the key values in order all the time. In that thread, people pointed out that there is ordinarily no reason to reuse key values, because there are plenty to waste in a long integer.

When first bringing together two legacy tables that were separate, meaningful keys that already exist are needed. I kept using mine, and got away with it so far, because my apps are standalone at the moment. But if I network the stuff, the RI better work, and Barbara says I shouldn't count on RI to cascade all changes. Now that I'm linking in still more tables, and overhauling other stuff, I'm starting to make surrogate keys.