>>Juan;
>>
>>Do you mean that if you have a person with Social Security # 111-11-1111, and then delete him, that you can't later add him, or another person with an SS# of 111-11-1111? If that's what your problem is, you can put a filter on your candidate for for NOT DELETED(). Then when a record is deleted, it is removed from the index as well.
>>
>
>Hey that's a cool trick that I hadn't thought of before. But wouldn't it be better (for rushmore optimization) to add the deleted function in the index expression instead of using a filter.
>
>Maybe it doesn't make any difference... Just a thought I had.


As others have mentioned, indexes with filters are not optimizable, and neither are indexes with NOT in them. In this case, you could have 2 indexes:

* not optimizable, but will enforce unique SS# but let you
* reuse deleted values
index on ssNo for not deleted() tag ssnoCand CANDIDATE

* Optimizable for queries
index on ssNo tag ssNo

If index bloat isn't a problem, this should work.