我正在一个项目中,数据库项目未被删除,但只标记为已删除.像这样的东西：
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约束只查看未标记为已删除的项目,那么我就解决了一个问题.
(当然,那时我必须处理当有人撤消删除时会发生什么……)
那么,我该如何定义那种约束呢？
解决方法
您可以在删除记录时将id值添加到名称的末尾,因此当有人删除id 3时,名称将变为Thingy3_3,然后当他们删除id 100时,名称将变为Thingy3_100.这将允许您在名称和已删除字段上创建唯一的复合索引,但是每当您显示它时都必须过滤名称列,并从名称末尾删除id.
也许更好的解决方案是使用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字段.
