Handling Deleted Records in a UNIQUE Index

 Last reviewed: June 27, 1995
Article 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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 27, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.