>>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.

Simple - what if I have a need to return the original record to the system after deletion? One of the reasons that I delete records from a file is to archive the record and move it to near-line storage, where it can be brought back later. Examples of archived things are 10 year old invoices, which are no longer needed for day-to-day operation, but might be wanted for some sort of historical study (we maintain line item detail about sales for a long time so that we can perform statistical analysis on market trends in our industry).

The theory is that the surrogate key, once assigned, always refers to the same thing, regardless.