Hey Walter,

This is just the kind of info I was looking for.

>>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.

The FOR NOT DELETED() filter didn't seem to work
when I tried it. Have you ever used it succesfully?

I'm trying to keep the size and complexity of my
CDX files to a minimum. I'm having some performance
problems with some of my merging apps now because of
my CDX's I think. So creating two indicies for the
same field may not be preferable.

I don't understand why they can't use filtered
expressions anyway?!?

>
>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.

...and haven't really solved the DELETED problem
at all
>
>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.

Yeah, I considered that, but for the reasons you've
outlined, I've discarded it.

>
>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.

Or at least offer this as an option.

What did you think about the
... + TRANSFORM( DELETED()) idea?

>>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.

Seemed so to me. I haven't revisited it since
I couldn't get it to work in the first place.
If you say that you've used it successfully, I'll
give it another shot.

...kt