Suggestion for enforcing unique constraint in a table where soft delete is implemented 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?
Why not somthing simple like this:



User enters position 'President' and it does not exist in the table.

Insert Into table



User deletes position 'President'. 

Update table set isDeleted = 1



User changes mind and again enters a position 'President'. 

Update table set isDeleted = 0