INDEX...UNIQUE should not be used to enforce uniqueness of a value as that is not what it does. INDEX...UNIQUE causes the index to only reflect one instance of each unique value of the index expression in the table. For example, one might use INDEX...UNIQUE to obtain a non-duplicated list of states from an Address table.
The "unique" index has some insurmountable problems. It cannot be depended on to be maintained dynamically. Because Fox only updates an index related to the record being altered, certain conditions can cause a Unique index to become incorrect. For example, a unique index on state will reflect one record for each state. In a table there may be 100 records for the state of NY, if you delete the one reflected in the unique index VFP will update the index by removing the reference to NY for that record, however since only the altered record is considered in the update of the index there will now be no reference for the state of NY in the index even though there are 99 records in the table for that state. (This situation can get even worse).
>I'm reasonably clear on how VFP uses indexes to prevent duplicate data entry but found out that it apparently applies such index controls to deleted records. Is there a way to set up VFP indexes to bypass or ignore deleted records? All responses are appreciated.
