Re-using soft deleted records

Question

If I have a table structure that is:

code, description, isdeleted

where code is the primary key.

The user creates a record, then later on deletes it. Because I am using soft deletes the isdeleted will be set to true. Then in my queries I would be doing a select with the where clause and not isdeleted

Now if a user goes to create a new record they may see that code 'ABC' doesn't exist so they tried to recreate it. The select statement won't find it because of the where clause. But there will be a primary key index error.

Should the user be allowed to re-use the record? I would think not since the idea of the soft delete is to keep the record for queries on older data so that joins to the 'deleted' record still work. If the user was allowed to re-use the code then they could change the description which might change the view of the historical data. But is it too harsh to stop them from using that code at all?

Or should I be using a completely hidden primary key and then the 'code' field can be re-used?

Answer 1

I know many people have argued that the data should be natural, but you should be using a primary key that is completely separate from your data if you're going to be supporting soft deletes without the intention of always re-using the previous record when this situation arises.

Having a divorced primary key will allow you to have multiple records with the same 'code' value, and it will allow you to "undelete" (otherwise, why bother with a soft delete?) a value without worrying about overwriting something else.

Personally, I prefer the numeric auto-incremented style of ID, but there are many proponents of GUIDs.