Unique index problem in VFP7

[dakotafox]

Our problem with the unique index violation error is occuring after a record is deleted from a table and a corrected record is trying to be inserted. The main index keys were not changed just some of the other field values, but how do we get Fox CDX to ignore the existence of the deleted record. SET DELETED ON is the application default so that all queries will not see deleted records. But the insert command seems to see them and stops the insert.

We have tried changing the value of a field value by adding DEL at the end of the field but this is not a good procedure and creates alot of other problems.

 

[rgbean]

First, I presume instead of a UNIQUE index (a leftover from the xBase heritage that should never be used), you are referring to a Primary or Candidate key index. In this case good design dictates that these keys should NEVER be reused, then you won't get into this problem. While you can programatically add a FOR NOT DELETED() qualifier on a Candidate index, you can't on a Primary key. The only other alternative is to PACK the table between anytime you want to DELETE and Reuse the same Primary key.

Rick

 

[DEDMOD]

Could you maybe add a prefix or suffix to your unique index when you delete it? This would let you have the original index value available to use.

Alternatively you could search your deleted records, RECALL the record and change whatever fields need changed. Dave Dardinger

 

[fluteplr]

IMHO. Whatever scheme you are using for keys, a key should NEVER be reused. If you follow this advice you problem will go away.