>>
>>This might be simplistic & I'm probably missing some important point, but rather than add a new record & delete the original - could you not simply overwrite the original. That way the primary key remains unchanged & there's no need for surrogate keys.
>>
>>Please let me know why this idea is wrong - I profess to being a beginner at this & need to learn.
>>
>>Paul.
>
>
>Still not a good idea. What happens if down the road you need to do an audit of somekind. What record was #456839? Potentially you could reuse the same ID many times. Now take that same ID and think about potential child records and you could have a real mess to clear up.

True, but I was assuming that as the original problem involved deletion & packing, then there would be no question of wanting historical information (e.g. for auditing)& any related tidying up of child records would be sorted prior to the deletion. In which case, there appears to be no significant difference in overwriting the original record & deleting then packing, except that overwriting doesn't require exclusive access to the table.

I must admit that I thought the idea of deletion/packing would have it's own problems, mainly for tracking old versions. I would have tended to index on version & revision - I guess this is a type of surrogate index anyway.

Paul