表中具有软删除列的唯一约束条件

Question

我有一张表格，其中包含以下列：

Id int
Name varchar *
Description varchar
LevelId int *
DeletedAt datetime nullable *

我希望在标有星号的上述字段上设置唯一约束：名称，LevelId和DeletedAt。我之所以添加DeletedAt约束是为了当有人软删除并添加具有相同名称和LevelId的新记录时，数据库会允许添加。但我错了，认为两行具有相同名称，LevelId和DeletedAt均为NULL的记录不会被允许，因为NULL不等于NULL。

我需要的是替代方案。如何支持此要求？我能想到的一件事是将DeletedAt替换为varchar，然后具有默认值，例如“Active”或空字符串（只是不是null），然后将已删除的行作为字符串放置日期。但我在想是否有更优雅的解决方案。

- g_b

---

在SQL Server中，唯一约束只允许一个NULL，正如您所期望的那样。 - Damien_The_Unbeliever

请参见例如此问题，这只是许多人正在寻求您（不无理）假定已经存在的行为。 - Damien_The_Unbeliever

我根据您上面的描述在SQL Server 2014中创建了一张表，并尝试插入两行匹配的名称、描述和删除时间。但是出现了错误信息：Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.T' with unique index 'IX_T'. The duplicate key value is (abc, def, <NULL>)。 - Damien_The_Unbeliever

请尝试为我们构建完整的脚本，以重现您的问题。最好是一个CREATE TABLE，CREATE INDEX和INSERT，可以无错误运行。就像我所说，当我根据您的描述构建这样的脚本时，我收到了错误2601，拒绝重复项。 - Damien_The_Unbeliever

好的，稍后我会发布，现在不行，非常感谢您的帮助。 - g_b

Answer 1

我建议使用独特的过滤索引来解决这个问题。对于您的具体情况，它应该是这样的：

create unique filtered index FUIX_Name_LevelId
   on dbo.yourTable (Name, LevelID)
   where DeletedAt is null;

这将只允许每个（名称，LevelId）元组中有一个“活动”行。它还允许尽可能多的“已删除”记录（因为这些行不符合索引上的过滤器，所以在确定唯一性时不予考虑）。

过去我遇到过的一个问题是：带有过滤索引的表需要特定的查询设置，否则对该表的查询将失败。请参见文档了解有关过滤索引的更多信息。

- Ben Thul

---

谢谢Ben，我会试一下看看它是否适合我。 - g_b

Answer 2

创建由3列组成的唯一复合键。 第一列是您的唯一列，第二列是LevelId，最后一列是DeletedAt（任何删除参数都可以是删除时间或数字值 - 如果已删除，则为增量值，如果未软删除，则为0）

CREATE UNIQUE NONCLUSTERED INDEX TABLE_NAME_DELETED_AT_NonClusteredIndex ON 
TABLE (
Name, LevelId, DeletedAt
) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

- Rohan Shenoy

Answer 3

我遇到了相同的情况，但需要使用两个条件进行过滤。但是过滤索引不支持OR条件，但支持AND运算符。

如果有人面临相同的情况，他们可以使用IN运算符来实现他们想要的结果。

例如：

create unique index FUIX_Name_LevelId
on dbo.yourTable (Name, LevelID)
where DeletedAt = 1 OR DeletedAt = 0; -- this will give an error.

--solution
create unique index FUIX_Name_LevelId
on dbo.yourTable (Name, LevelID)
where DeletedAt in (1, 0); 

- cdev