Craig,

Over 10 weeks later, I think I've finally solved this duplicate keys problem. On the "CDX Corruption Checklist" (on wiki written by Steven Black I think), one of the many check list items mentions a problem with duplicate primary keys when using updatable local views. It apparently is a "known bug" - but obviously, I didn't know about it. If the first field in the view's base table is the primary key field (an integer), and, in a multiuser environment the stars and planets align just so, a CHR(26) (ie. end of file) is written to the first byte of the integer over-writing its least significant byte which could cause this integer to be a duplicate. At least, that what I understand from the checklist text.

I simply moved the primary key integer field so that it was NOT the first field in the table. That was a week ago and haven't seen a duplicate since. May be too soon to be absolutely sure.

thanks for your help.
Alan

PS. Enjoyed the dinner at the Hawthorne Inn when you were up in CT.


>Have you checked for DELETED() records?
>
>
>>I have a table, INVOICE, with a primary key tag on invoice_id. When attempting to insert a new row with a duplicate invoice_id value (or changing an existing row's invoice_id to a duplicate invoice_id), I get the expected "Uniqueness of index INVOICE_ID is violated".
>>
>>However, several times a week, I'll run the following query:
>>SELECT invoice_id,count(*);
>> FROM Invoice;
>> GROUP BY 1;
>> HAVING count(*)>1
>>
>>and will find a few duplicate invoice ids - yet no errors ever occurred that I know of - nothing in the error log.
>>
>>For example, the above query might show that invoice_id 13 appears twice. When I LOCATE FOR invoice_id=13, I'll find only one (that is, CONTINUE goes to end of the invoice table) If I LOCATE FOR invoice_id=13 NOOPTIMIZE in conjunction with CONTINUE, I'll find both instances of ID=13 - as long as I don't use the index to find the duplicate ids.
>>
>>So, I'm guessing that the value in the index was updated with a 13 while the table continues to have a value of 7, for example. Or vice-versa. How can this happen? Are there any known "loop-holes" in VFP that might allow this?
>>
>>I should also note that I have code in the INSERT,UPDATE,DELETE triggers of the invoice table that log (audit) any activity to any field in the table. There is no record of the invoice_id field being changed.
>>
>>Any ideas/suggestions are much appreciated!
>>Thanks,
>>Alan