Suggestion for enforcing unique constraint in a table where soft delete is implemented [duplicate]

Question

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?

Answer 1

Why not somthing simple like this:

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

     Insert Into table
     

• 2. User deletes position 'President'.

     Update table set isDeleted = 1
     

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

     Update table set isDeleted = 0
     

Answer 2

CREATE TABLE Position (
  Id int NOT NULL PRIMARY KEY IDENTITY(1,1),
  Name nvarchar(255) UNIQUE,
  IsDeleted bit NOT NULL DEFAULT 0
)

INSERT INTO Position (Name) VALUES ('Titi'), ('Toto'), ('Tata')
UPDATE Position set IsDeleted = 1 where Name='Toto'

DECLARE @Name nvarchar(255)
SET @Name = 'Toto'

-- INSERT Position (Name) VALUES (@Name) --RAISES AN ERROR

IF EXISTS(SELECT * FROM Position WHERE Name = @Name) 
  UPDATE Position SET IsDeleted = 0 WHERE Name = @Name
ELSE
    INSERT Position (Name) VALUES (@Name)

SELECT * FROM Position