>A records that's been flagged as deleted still exists as far as it's key values go. If you're going to have to reuse keys, I'd recommend that you either change the value of the primary key to an arbitrary, unique nonsense value on deletion, or check to see if the record already exists and has been deleted before INSERTING, and if necessary, RECALL the record and then replace the appropriate values.
>
>I tend to use surrogate primary keys (keys that are unique and carry no detail about the record other than providing a unique identifier, typiczlly an integer field that is unioquely assigned when the record is added) and don't recycle the keys.

Why assign a new surrogate key to a recycled record? If the purpose of a surrogate key is to be a unique representative of a record - well, after recalling, it's still the same record, and, IMO, it should keep its PK value. We may blank out all the other fields (we actually should). This is just another occasion where we try to change the PK value - what we strictly forbid in all other cases. Why should we change it now? Surrogate keys are not (supposed to be) used for ordering, either.