>>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?

I've got a little bit different but still associated with the above issue question. There's a user-entered field that should be unique. So, I use candidate index which is checked when user tries to save changes with tableupdate(). The bad thing is that deleted records are also checked unless I use filter for not deleted() (goodbye Rushmore :( ). The workaround that I see is to use two indices on that field (w/ and w/o filter).

Does anybody have better solutions?

Thanks in advance.