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