3

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?

  • 1
    Not exactly a duplicate but the answer is useful. Add a unique filtered index instead of a constraint. – Mikael Eriksson Aug 26 '15 at 7:24
  • And this one with the extended answer in a comment. dba.stackexchange.com/questions/94931/… – Mikael Eriksson Aug 26 '15 at 7:25
  • 2
    You're better off having a position_history table and moving deleted items there – Neil McGuigan Aug 26 '15 at 8:15
  • @Neil McGuigan: Unfortunately, the position ID is used in other tables as FK so I can't move them. – g_b Aug 26 '15 at 22:49
0

Why not somthing simple like this:

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

      Insert Into table
      
    1. User deletes position 'President'.

      Update table set isDeleted = 1
      
    1. User changes mind and again enters a position 'President'.

      Update table set isDeleted = 0
      
  • Thank you for the answer. I also thought of this but in my scenario, this won't do. – g_b Aug 26 '15 at 22:34
0
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
  • Thanks for answering. However, there might be some instances in which the deleted position has some related records via a FK. Now the new position entered should be a fresh one, so this should have no records yet related to it. This query will also bring back related records with it. I need it to be really new while not deleting the old one. – g_b Aug 26 '15 at 22:40
  • Ok so as they say, put an unique index on Name and isDeleted = 0 so it will break the unicity and thus raise an error if you try to add the same name with the delete flag set to false. Damn simple ! – user2955677 - Greg Aug 27 '15 at 8:13
  • As I stated in my original question: "Even if I use Name and IsDeleted combination for unique constraint, there is no stopping the user from deleting again and adding several times". So this will not really work. – g_b Aug 27 '15 at 10:12
  • So when a user deletes "President" (id = 5) you update isDeleted to 1, foreign keys with id = 5 remain. Then the user re-enters "President" you can either unflag the existing item (id = 5) by updating isDeleted to 0 or, re-create an item "President" with a new id. Do you see a third option ? – user2955677 - Greg Aug 27 '15 at 15:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.