Resolving Soft Delete Unique Key Conflicts
The scenario here is that I never want users to delete data. Instead a delete will just hide items. However, the issue becomes that insertions fail for deleted items since they will still exists within the database. However, I still want all active items to respect unique key constraints. So I was thinking since the NULL value does not respect unique key constraints, that adding a field to my unique key constraint such as; activated would solve the issue.
unique key example:
- activated NULL
So when a something is deleted activated will be set to NULL, allowing new items to be added that may have the exact same signature. All active items will have a value of 1. Is this generally a good approach? This will be an internal safety measure making it possible to retrieve data that has been accidentally deleted through my applications user interface. I will than have a separate, development only purge method to ride the database of soft-deleted items, when necessary to completely remove items.
Also, I'm thinking it would be good to make activated an enum value, since it should only ever have a single value, 1 when it has a value. Thoughts on that? I know the general idea is to use tinyint but that *could* result in values other than 1. By using an enum its an absolute tha value will always be 1.