What to do when I want to use database constraints but only mark as deleted instead of deleting?

Question

I am working in a project where database items are not deleted, but only marked as deleted. Something like this:

id   name     deleted
---  -------  --------
1    Thingy1  0
2    Thingy2  0
3    Thingy3  0

I would like to be able to define something like a UNIQUE constraint on the name column. Seems easy, right?

Let's imagine a scenario in which "Thingy3" is deleted, and a new one is created (perhaps years later). We get:

id   name     deleted
---  -------  --------
1    Thingy1  0
2    Thingy2  0
3    Thingy3  1
...
100  Thingy3  0

From the user's point of view, he deleted an item and created a new one. Much like deleting a file, and creating a new file. So it's obvious to him that the new item is unrelated and unattached to any data connected to the old item.

That's already handled, since the DB only cares about the id, and since the new item has an id of 100 instead of 3, they are utterly different.

My difficulty arises when I want to prevent the user from creating another "Thingy3" item. If I had a UNIQUE constraint that only looked at items that aren't marked deleted, then I would have solved one problem.

(Of course, then I'd have to deal with what happens when someone does an undo of the delete...)

So, how can I define that sort of a constraint?

Answer 1

Not sure about SQLServer2005, but you can define compound constrainst/indexes

CREATE UNIQUE INDEX [MyINDEX] ON [TABLENAME] ([NAME] , [DELETED])

As pointed out by SteveWeet, this will only allow you to delete/create twice.

Answer 2

For example, you can add an illegal character (*) to the deleted name. But you still have problems undeleting a deleted item. So probably the better idea is to prohibit double names even if they are deleted.

You can clean the deleted records after an amount of time (or move them to a separate table).

Answer 3

Create a unique constraint on (name, deleted). This will mean you can only have one deleted per name, however.

The obvious work-around for that works under ANSI-92 but not on MS SQLServer: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299229

Answer 4

For a simple table called [Test] with columns ID(int), Filter(nvarchar), Deleted(bit)

ALTER TABLE [dbo].[Test] ADD 
    CONSTRAINT [DF_Test_Deleted] DEFAULT (0) FOR [Deleted],
    CONSTRAINT [IX_Test] UNIQUE  NONCLUSTERED 
    (
        [filter],
        [Deleted]
    )  ON [PRIMARY]

Answer 5

Add a random hash after the unique name. Something that is easily reversible. Possibly separate with an underscore or some other character.

Edit after comment: You could simply add underscore and the current timestamp.

Answer 6

You could add the id value to the end of the name when a record is deleted, so when someone deletes id 3 the name becomes Thingy3_3 and then when they delete id 100 the name becomes Thingy3_100. This would allow you to create a unique composite index on the name and deleted fields but you then have to filter the name column whenever you display it and remove the id from the end of the name.

Perhaps a better solution would be to replace your deleted column with a deleted_at column of type DATETIME. You could then maintain a unique index on name and deleted at, with a non-deleted record having a null value in the deleted_at field. This would prevent the creation of multiple names in an active state but would allow you to delete the same name multiple times.

You obviously need to do a test when undeleting a record to ensure that there is no row with the same name and a null deleted_at field before allowing the un-delete.

You could actually implement all of this logic within the database by using an INSTEAD-OF trigger for the delete. This trigger would not delete records but would instead update the deleted_at column when you deleted a record.

The following example code demonstrates this

CREATE TABLE swtest (  
    id   INT IDENTITY,  
    name  NVARCHAR(20),  
    deleted_at DATETIME  
)  
GO  
CREATE TRIGGER tr_swtest_delete ON swtest  
INSTEAD OF DELETE  
AS  
BEGIN  
    UPDATE swtest SET deleted_at = getDate()  
    WHERE id IN (SELECT deleted.id FROM deleted)
    AND deleted_at IS NULL      -- Required to prevent duplicates when deleting already deleted records  
END  
GO  

CREATE UNIQUE INDEX ix_swtest1 ON swtest(name, deleted_at)  

INSERT INTO swtest (name) VALUES ('Thingy1')  
INSERT INTO swtest (name) VALUES ('Thingy2')  
DELETE FROM swtest WHERE id = SCOPE_IDENTITY()  
INSERT INTO swtest (name) VALUES ('Thingy2')  
DELETE FROM swtest WHERE id = SCOPE_IDENTITY()  
INSERT INTO swtest (name) VALUES ('Thingy2')  

SELECT * FROM swtest  
DROP TABLE swtest

The select from this query returns the following

id      name       deleted_at
1       Thingy1    NULL
2       Thingy2    2009-04-21 08:55:38.180
3       Thingy2    2009-04-21 08:55:38.307
4       Thingy2    NULL

So within your code you can delete records using a normal delete and let the trigger take care of the details. The only possible issue (That I could see) was that deleting already deleted records could result in duplicate rows, hence the condition in the trigger to not update the deleted_at field on an already deleted row.

Answer 7

The problem with a compound unique constraint is that it's not possible to have multiple records with the same name that are deleted. This means that the system will break once you delete a third record. I wouldn't recommend appending stuff to the names because, theoretically, a duplicate situation could arise. Also, by doing so, you are basically corrupting the data in the database as well as adding cryptic business logic to the data itself.

The only possible solution, database wide, is to add a trigger that checks that the inserted/updated data is valid. It's also possible to put the checks outside of the database, into code.

Answer 8

It might be worth considering using a "recycle bin" table. Instead of keeping the old records in the same table with a flag, move them to its own table with its own constraints. For instance, in the active table you do have a UNIQUE constraint on name, but in the recycle bin table you don't.

Answer 9

Instead of deleted column use end_date column. When user deletes a record add the current date in end_date column. Any records where end_date column is NULL are your current records. Define a unique constraint on two columns name and end_date. Due to this constraint you never have a scenario where valid record name is duplicated. Any time user wants to undelete a record, you need to set the end_date column to null and if this violates the unique constraint then you show a message user to user that the same name already exists.

Answer 10

The type of constraint you require is a table-level CHECK constraint i.e. a CHECK constraint consisting of a subquery which tests NOT EXISTS (or equivalent) for the table e.g.

CREATE TABLE Test 
(
   ID INTEGER NOT NULL UNIQUE, 
   name VARCHAR(30) NOT NULL, 
   deleted INTEGER NOT NULL, 
   CHECK (deleted IN (0, 1))
);

ALTER TABLE Test ADD
   CONSTRAINT test1__unique_non_deleted
      CHECK 
      (
         NOT EXISTS 
         (
            SELECT T1.name
              FROM Test AS T1
             WHERE T1.deleted = 0
             GROUP
                BY T1.Name
            HAVING COUNT(*) > 1
         )
      );

INSERT INTO Test (ID, name, deleted) VALUES (1, 'Thingy1', 0)
;
INSERT INTO Test (ID, name, deleted) VALUES (2, 'Thingy2', 0)
;
INSERT INTO Test (ID, name, deleted) VALUES (3, 'Thingy3', 1)
;
INSERT INTO Test (ID, name, deleted) VALUES (4, 'Thingy3', 1)
;
INSERT INTO Test (ID, name, deleted) VALUES (5, 'Thingy3', 0)
;
INSERT INTO Test (ID, name, deleted) VALUES (6, 'Thingy3', 0)
;

The last INSERT (ID = 6) will cause the constraint to bite and the INSERT will fail. Q.E.D.

...ooh, nearly forgot to mention: SQL Server doesn't yet support CHECK constraints with that contain a subquery (I tested the above on ACE/JET, a.k.a. ). While you could use a FUNCTION I've read this is unsafe due to SQL Server testing constraints on a row-by-row basis (see David Portas' Blog). Until this full SQL-92 feature is supported in SQL Server, my preferred workaround is to use the same logic in a trigger.