Deleting a field in database by setting an is_deleted field and using index

Question

Hi,

I'm writing an app which I want to delete records by setting an is_deleted field in the database to true and is defaulted to false.

This works fine but I'm running into an issue when I index unique another field I can't insert a field with the same entry although the old one is practically deleted but not from DB point of view.

Do you kno w a solution around that?

Thanks,

Tam

Answer 1

Make a unique index over the combination of is_deleted and whatever other field that you actually need to be unique.

Answer 2

If you want to "reuse" the deleted row's "identity", then you'd have to clear that identity field (whatever it is) when you "soft delete" a row. Not really a good idea in my opinion, since if you ever what to undelete that row - then what??

Why do you want to reuse those identities? I would stay away from such a construct - just give each row its own identity - whether it's active or deleted - and don't re-use pre-existing identities. Not a good practice!

Answer 3

Another option is to change the column to a date field such as deleted_at. Creating your unique constraint on the identity + date should be unique enough.

Answer 4

If you require the ability to re-use the identifiers, then your database should not have a unique constraint on the column. Uniqueness would have to be guaranteed by your application; If you really want the DB to do it, the only way I can think of that would always work would be a trigger that verifies uniqueness but only on the columns with IsDeleted = 0.

Answer 5

As others have alluded to, there isn't a really great way of doing this. Even if you span your unique index across the is_deleted and identity fields, you can still only have one deleted object at a time.

An alternative approach would be to add a non-unique field to which the id would be assigned. To delete something you would run something like this:

update table
   set old_id = id, id = null, is_deleted = 1
 where id = ?

And to restore it, as Mr. Haynes liked to say, the installation is the reverse of the removal procedure.

Note that some databases (*cough* MSSQL cough) don't allow multiple NULL values in a unique-constrained column, so you'll have to be more clever.

Answer 6

I wouldn't use Pavel's approach because you can't delete the second row (because the first column has already the key combination of ID and FALSE).

So if you need to have a unique ID, create an identity column, which is truly unique. Your current id column would be than just indexed (allowing duplicates). It sounds like your current ID column is an external ID so name and treat it accordingly.