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