Keith,

The common wisdom is that unique keys are unique. Period. Why re-use them when they're so cheap.

I fyou have something like an invoice number and want to replace one invoice with a new one with the same number, don't use the invoice number for the key. Use an auto-generated one which the user never sees, and save the invoice number for something the user does see.


>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.
>
>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. 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.
>
>Craig, I'm sure you have an article in your
>knowledge base. I'm going to look now ;-))
>
>Thanks in advance...
>
> ...kt