Keith,

>What is the common wisdom for handling deleted
>records with respect to unique keys? When you
>go to INSERT a record with an identical key as
>a deleted on you get a uniqueness error.

IMO, there is no common wishdom. When you're using intelligent keys, you'll have the add a FOR !DELETED() filter to the primary index. To make rushmore work, you'll have to have another (regular) index on the same field.

If using artificial keys, you won't need the FOR !DELETED() filter on the primary index, however, you'll have to force the logical intelligent key to remain unique. The most efficient way to do this is by a Candidate key. However here you still have to deal with the DELETED() records. So you'll have to add a FOR !DETELED() filter to the candidate key. In fact you're shifting the problem from the primary key to the candidate key.

Another way is to force uniqueness at your business logic. But this is not as secure as a candidate index. One bug in your application (or another hacking your table via for e.g. ODBC or VFP) might cause the ship to sink.

IMO, This whole DELETED() matter is a big shortcomming in VFP. It would be nice if we could regard a deleted record as a NON-existing record.


>I have tried filtering on NOT DELETED(), but 1)
>it doesn't work, and 2) you can't use the
>index in otherwise optimizable queries.

1. It should work.
2. Add another regular index on the field

I have
>also tried prefixing one of the fields with a
>character (!, ~, X, etc) to keep the record unique
>with varying levels of success, 3) having those
>two reasons, I didn't really need to find
>anymore :-)

>Deleting records in this application is fairly
>uncommon so I don't want to have to write scads
>of code to reuse deleted records.

Walter,