i usually only use primary / unique keys if the system is generating them. if a field needs to be unique, i enforce that separately. i'd use computer generated key for unique id for record number, and get invoice number (as separate field) when record is saved.

>>>>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.
>
>Thanks, Edward. I tried that approach and here some of my thoughts:
>
>1. This works only with views. When I used table (ether with record or table buffering) I couldn't move record pointer to check if the record with the same field value is deleted. The error "The uniqueness of ...bla,bla,bla violated" occured when I tried to do that.
>
>2. What if user doesn't add a record but changes the existing one? How do I recycle the deleted record? I tried to REPLACE the unique field of the deleted record with the STR(primary key)(I use surrogate Integer keys) of that record, assuming that the field is a string and user can't put numbers in it. So the value would be unique.But it won't work with numeric fields.
>
>Thanks, again.