Larry, Manuel,

Though I agree that in many cases generated PK are better there are circumstance where 'intelligent' are better. For example having a generated PK voor a intelligent key on a date field does not make sense, especially if this date is used in foreign keys. It might break down your whole RI mechanism.

So, the solution to this problem might be to add two indices:
- One normal to enable rushmore optimization.
- One Primary to enforce uniqueness of the key.

In fact your solution does exactly the same except indexing the generated PK.

Walter,


>The better way is to not reuse primary keys.
>
>The Deleted flag does nothing to inhibit the primary index uniqueness rule. If you reuse the same key values, in order to bypass this, you need to add the FILTER condition to the index as you did. However, this makes the index unusable by Rushmore. If you use this table in any joins, none of the SQL statements will be optimized.
>
>As I said, the better way is to not reuse keys. If you delete a record, that key is gone forever. You can use GUIDs for this or create a table of keys and use this to get the next key for a new record (the appropriate locking would have to be performed so multiple people could not get the same PK).
>
>If the keys need to be unique, you could always make the index a candidate key and add the FILTER criteria to it. It wouldn't be used to join to other tables so the Rushmore usability would not be a factor.
>
>HTH.
>
>>I ran into a particular problem that I have trying to figure it out.
>>
>>I have a table with a primary index.
>>I can add to the table. If I delete the record and cannot add another record.
>>I get the Error Index Uniqueness. If I pack the table. I can add the record.
>>Then I start the process again.
>>
>>I have set delete on -- does not work in this problem.
>>
>>What I ended up doing was to add to the filter option of the index
>>tag the expression !DELETED().
>>
>>This allowed me to by passed my problem
>>
>>However, I have a question.
>>
>> Is this approach correct or is there a better way?
>>
>>Manny