SQL 约束验证唯一值

Question

我有一个表，我需要限制插入的数据，我已经使用了触发器，但我想知道是否可以通过约束来完成相同的任务。

字段是:

Id
Date
Passport
...
Deleted

当已删除字段为1时，约束必须允许n条记录，但如果已删除字段为0 必须只有一行具有相同的日期和护照。

应该适用于以下步骤:

1. 添加 Id=1、Date=2018-05-01、Passport=MPEUIE80、Deleted=0 的行
2. 删除 Id=1 的行，因此“已删除”字段将为 1
3. 添加 Id=2、Date=2018-05-01、Passport=MPEUIE80、Deleted=0 的行
4. 删除 Id=2 的行，因此“已删除”字段将为 1
5. 添加 Id=3、Date=2018-05-01、Passport=MPEUIE80、Deleted=0 的行
6. 添加 Id=4、Date=2018-05-01、Passport=MPEUIE80、Deleted=0 的行

直到第五步，一切正常，但在最后一步一定有错误，那是因为我无法处理具有相同日期、相同护照<的两行/strong> 且均带有 已删除=0

提前致谢。

Answer 1

您可以使用经过过滤的唯一索引:

create table [t](
    [id] [int] NULL,
    [date] [datetime] NULL,
    [passport] [char](8) NULL,
    [deleted] [int] NULL
)

create unique index unq_t_date_passport on t(date, passport)
    where deleted = 0;

编辑:

如果筛选索引在您的 SQL Server 版本上不起作用，那么您可能需要检查 compatibility level 。只要兼容性设置为 100 或更高，它就应该可用。您可以使用以下方法检查您的服务器版本和兼容性级别:

SELECT SERVERPROPERTY('ProductVersion');
SELECT name, compatibility_level FROM sys.databases;

还有另一种方法可以从 SQL Server 2005 开始使用，假设 id 是唯一且非负的。它使用计算列来执行与过滤索引基本相同的操作:

alter table t
    add column deleted_id (case when deleted = 0 then -1 else id end) persisted;

create unique index unq_t_passport_date_deleted_id on t(passport, date, deleted_id);