Uniquess of Index violated 1

[foxnet]

I have a form that I keep getting this error message. I can create the problem by deleting a record and re-adding it with the same key. The table is using a "Candidate" index and the form has SET DELETED ON. If I change the index to "Regular", I can re-add the record. Is there something I need to do diffently when deleting a record using a candidate index.

Thanks

 

[mspratt]

Hi foxnet,

Deleting a record only marks it as deleted. You have to purge the table to really get rid of it. Just don't reuse the key.

Regards,

Mike

 

[foxnet]

Mike,

If I have SET DELETED ON, should I be able to reuse the key. I now I can do that for a regular index it is just the candidate index that is giving me the error.

 

[Stella740pl]

No, even with SET DELETED ON you wouldn't be able to reuse the key. INDEX is not affected by SET DELETED ON. All records are indexed (unless index expression specifies otherwise).

I haven't tried INDEX ... FOR !DELETED(), though; you can try, but not sure it would help.

A regular index does not require uniqueness of the key.

 

[mspratt]

Probably because you can recall the record, primary and candidate keys MUST be unique. Even if deleted. Been that way almost forever.

Regards,

Mike

 

[Stella740pl]

foxnet,

I found a moment to try INDEX ... FOR !DELETED(); it works the way you need it.

Thanks for the star. Looks like it works for you, too.

 

[Olaf Doschke]

Hi Stella and others,

the thing with filtered indexes is, that they won't be used for rushmore optimizations, which makes it a poor solution in many cases. It may be okay for smaller tables, though.

Bye, Olaf.

 

[Stella740pl]

Hi, Olaf,

I agree that the best thing would be just not to reuse the key, or maybe, depending on the task, to use regular index.

But the task was to enable reusing the key of a deleted record while using candidate index, so...

Personally, I have never used filtered index for anything, and stopped using SET FILTER long ago; it slows down the performance greatly.

Stella.