I am using SQL Server 2014 and in my database, I have a Position table which is structured like this:
ID int PK
Name nvarchar(50) Unique Index
IsDeleted bit
A user can't enter positions with similar names; IsDeleted is a flag for soft delete as I want to avoid deleting records from this table. However it got me thinking in a scenario like this:
1. User enters position 'President'.
2. User deletes position 'President'.
3. User changes mind and again enters a position 'President'.
This would result in an error due to unique constraint. Even if I use Name and IsDeleted combination for unique constraint, there is no stopping the user from deleting again and adding several times. One thing I can think of is having Name and TimeStamp combination as unique constraint, but it doesn't sound correct.
I'd like to enforce this if possible in the database, and not the application logic. Can you guys give some suggestions?