>>One problem occurs if you use primary or candidate indexes. Since the deleted record is still indexed trying to add a new record that duplicates a key value of a deleted record will cause an uniqueness violation error. This is a problem if a user deletes a record by mistake then tries to add a new record to correct the error. Using surrogate keys is the best way to avoid this problem.
>
>This is very helpful for a couple of reasons. It warns me what to look out for in the future and, you may have just solved a problem my friend has had literally for years with deleted/readded records that spontaneously disappear from a table. Not sure if your example applies to the old Foxpro 1.x stuff but what you describe sure sounds familiar. At least its another angle to check out. Thanks Steve.

The problem I described involves VFP only. Before VFP all Fox had was a Unique index. It sounds like it does the same thing as a primary or candidate index. However it doesn't work that way. Primary / candidate indexes will not allow duplicate key values. The Unique index will allow duplicate keys but just puts one the records in the index. In other words you can have duplicated keys but the index will only see the first record with that kay.

If you delete a record then add it back again. The Unique index will still point to the deleted record. If you access the table with a unique index, the record you just added will be invisible and with DELETED ON you won't see the deleted record either.

The Unique index is almost worthless because it doesn't prevent duplicates and just tries to hide them. I never used Unique indexes because of the problems. However primary and candidate indexes are very useful