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约束只查看未标记为已删除的项目,那么我就解决了一个问题.

(当然,那时我必须处理当有人撤消删除时会发生什么……)

那么,我该如何定义那种约束呢？

解决方法

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

您显然需要在取消删除记录时进行测试,以确保在允许取消删除之前没有具有相同名称的行和null deleted_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      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字段.

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!