I am getting Primary Key violations on my tables from time to time. The application is written in VFP 3.0 and uses surrogate keys of type Integer. There is a stored procedure in the database, NextId() that grabs the next ID for a record. This function is set in the default value property of the tableID field, and a Primary index is set on this field. Pretty standard stuff.
I am having a problem with Index corruption and then the Primary Key is somehow getting reused. When I reindex the table I get the invalid primary key error and can't open the table anymore. I think the problem is caused by lock ups and crashes that corrupt the indexes. We have Stonefield Database Toolkit 3.0, but I haven't implemented it as part of the application. I just created a Datafix Utility program to fix errors. This approach is not working well, because it doesn't fix the duplicate primary key problem.
Are there any suggestions on how to detect index corruption and deal with these problems in a better way, possibly on the fly? How can I fix the duplicate Primary Key problem automatically once it happens? I can't open the table or drop the key with this error. And if I drop the table and delete the CDX file I loose my long file names,which I could write a program to deal with, but that doesn't seem like the right way to go. Any suggestions or similar experiences would be greatly appreciated.

Shane