>>< SNIP >
>>
>>>You can also change the key value before deleteing a record. FOr example a numeric key could be made negative when the record is deleted.
>>>
>>
>>Changing the key works, but making it negative doesn't, for the simple reason that if you ever recycle a key twice, there will be two identical negative values...
>>
>>>The index could be made a composite key that includes the deleted status as part of the key.
>>
>>Same problem.
>
>Ed,
>I guess I didn't think those options all the way through. I've always used either the DELETED OFF option or surrogate primary keys.
It helps if you try them and get bit once or twice... < g > The critical message is not to recycle primary keys. Surrogate keys make this easy, and once you give up on the idea that they're needed for sequencing, generating them safely becomes easier.