当我想使用数据库约束但只标记为已删除而不是删除时该怎么办？ [英] What to do when I want to use database constraints but only mark as deleted instead of deleting?

问题描述

我在一个项目中工作，其中数据库项目未删除，但只标记为已删除。像这样：

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

我想能够在 name 列上定义一个UNIQUE约束。看起来很容易，对不对？

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

让我们想象一个场景，其中Thingy3被删除，并创建一个新的（可能多年后）。我们得到：

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.

这已经处理，因为DB只关心<$因为新项目的 id 是100而不是3，所以它们是完全不同的。

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.

当我想阻止用户创建另一个另一个Thingy3项目时，会出现我的困难。如果我有一个UNIQUE约束，只看看没有标记删除的项目，那么我会解决一个问题。

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?

推荐答案

当记录被删除时，您可以将id值添加到名称的末尾，所以当有人删除id 3的名字变成Thingy3_3，然后当他们删除id 100的名字变成Thingy3_100。这将允许您在名称和已删除字段上创建唯一的复合索引，但是您必须在显示名称列时对其进行过滤，并从名称末尾删除该标识符。

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.

也许更好的解决方案是用DATETIME类型的deleted_at列替换您删除的列。然后，您可以在名称上维护一个唯一索引，并在其中删除，其中未删除的记录在deleted_at字段中具有空值。这将阻止在活动状态下创建多个名称，但会允许您多次删除同一名称。

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.

您显然需要在取消删除记录时进行测试以确保在允许取消删除之前没有具有相同名称的行和一个null deleted_at字段。

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.

您可以在数据库中实际实现所有这些逻辑使用INSTEAD-OF触发器进行删除。

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.

以下示例代码演示了此

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  

返回以下

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

您可以使用正常删除删除记录，并让触发器处理详细信息。唯一可能的问题（我可以看到），删除已删除的记录可能会导致重复行，因此触发器中的条件不更新已删除的行上的deleted_at字段。

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.

这篇关于当我想使用数据库约束但只标记为已删除而不是删除时该怎么办？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！