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