>>>>>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.
>
>So what do you think of candidate indexes? In a previous thread on a similar subject, I was asking about the need for surrogate keys and also asked whether a candidate index was a useful tool or a potential source of trouble. Barbara said that they were trouble, I think for the same reason that a meaningful primary key can be trouble. I suppose that in a heavy multi-user environment (which is outside my experience), the RI can't keep up and corruption ensues. I was also wondering, if one doesn't use a candidate index, should one use a stored procedure to ensure uniqueness of a certain field?
>
>I always quote Barbara because she gave me the most advice in the past about keys.

I like surrogate keys. In regard to candidate keys, I would think that it's difficult to provide real multi-user uniqueness without having one.