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

You could use following solution:
1. You have candidate index (no filter).
2. When you try to save a record and trap duplicate key error, you can check deleted() status of the existing record and, if deleted=.t., recycle it.