Handling Deleted Records in a UNIQUE Index

ID: Q98696

The information in this article applies to:

Microsoft FoxPro for Windows, versions 2.5 and 2.5a
Microsoft FoxPro for MS-DOS, versions 1.02, 2.0, 2.5, and 2.5a

In FoxPro, if a database has a UNIQUE index and a record is deleted but the database is not packed, any additional undeleted records that have the same key value as the deleted record will not be accessed by FoxPro while the UNIQUE index is the controlling index. SET DELETED OFF will display the deleted record; SET DELETED ON will not display any records with the deleted key field.

In order to use the INDEX ON ... UNIQUE command and maintain the ability to use all valid undeleted records in the database, include an IIF statement in the index that identifies all deleted records. For example:

   INDEX ON IIF(DELETED(),"0","1")+CNO TO TEST.IDX UNIQUE

This command causes all the deleted records to be placed first if SET DELETED ON is in effect. If SET DELETED OFF is in effect, all undeleted UNIQUE records will be ordered on the CNO field.

NOTE: When a UNIQUE index (or index tag) is active and a duplicate record is changed in a way that affects its index key, the index or index tag is updated. However, the next duplicate record with the ORIGINAL index key cannot be accessed or displayed until you have reindexed the file. If a record is deleted or changed once the index has been created, the index must be REINDEXED to reflect these changes.

Additional reference words: FoxDos FoxWin 1.02 2.00 2.50 2.50a delete non- deleted KBCategory: kbprg KBSubcategory: FxprgGeneral

Last Reviewed: June 27, 1995