当我想使用数据库约束但仅标记为已删除而不是删除时该怎么办？

小编典典

当我想使用数据库约束但仅标记为已删除而不是删除时该怎么办？

sql

我正在一个项目中，其中的数据库项目不会被删除，而只会标记为已删除。像这样的东西：

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

我希望能够在name列上定义类似UNIQUE约束的内容。看起来很容易，对不对？

让我们想象一个场景，其中删除了“ Thingy3”，并且创建了一个新的场景（可能是几年后）。我们得到：

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

从用户的角度来看，他删除了一个项目并创建了一个新项目。就像删除文件，然后创建一个新文件一样。因此，对他来说显而易见的是，新项目是不相关的，并且未附加到与旧项目相关的任何数据上。

由于数据库仅关心id，并且新项的id值为100而不是3，因此已经解决了，它们是完全不同的。

当我想阻止用户创建 另一个 “
Thingy3”项目时，我的困难就出现了。如果我有一个UNIQUE约束，只查看未标记的项目deleted，那么我将解决一个问题。

（当然，那我必须处理当某人撤消删除操作时发生的情况…）

那么，如何定义这种约束呢？

阅读 346

2021-03-17

共1个答案

小编典典

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

也许更好的解决方案是将您的Deleted列替换为DATETIME类型的Deleted_at列。然后，您可以在name上保留唯一索引，并在at删除它，在Deleted_at字段中使用未删除的记录具有空值。这样可以防止在活动状态下创建多个名称，但允许您多次删除相同的名称。

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

通过使用INSTEAD-OF触发器进行删除，您实际上可以在数据库中实现所有这些逻辑。此触发器不会删除记录，但是会在您删除记录时更新Deleted_at列。

以下示例代码演示了这一点

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名称Deleted_at
1 Thingy1空
2 Thingy2 2009-04-21 08：55：38.180
3 Thingy2 2009-04-21 08：55：38.307
4 Thingy2空

因此，您可以在代码中使用常规删除来删除记录，并由触发器来处理细节。唯一可能的问题（我可以看到）是，删除已删除的记录可能会导致重复的行，因此触发器中的条件是不会更新已删除的行上的delete_at字段。

2021-03-17