如何创建检查约束以确保一个表的活动详细记录？ [英] How to create a check constraint that ensures one active detail record for a table?

问题描述

比方说我有这个表格：

InvoiceDetailId  (int, not null, PK),
InvoiceId        (int, not null, FK),
InvoiceNumber    (varchar(50), null),
EndEffectiveTime (datetime, null)

我如何在此表上写一个检查约束，以确保对于该表中的每张发票，只有一条记录的EndEffectiveTime为空（这是活动明细记录） ？

How can I write a check constraint on this table that makes sure that for each invoice in the table there is only one record with a null EndEffectiveTime (meaning it's the active detail record)?

所以基本上此查询不能从不返回结果：

So basically this query can never return results:

SELECT   InvoiceId
FROM     InvoiceDetails
GROUP BY InvoiceId, EndEffectiveTime
HAVING   (EndEffectiveTime IS NULL)
         AND (COUNT(InvoiceDetailId) <> 1);

推荐答案

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

You can use a unique filtered index.

create unique index UX_InvoiceDetails_InvoiceId on InvoiceDetails(InvoiceID) 
  where EndEffectiveTime is null

创建唯一索引

创建过滤的索引

SQL提琴

这篇关于如何创建检查约束以确保一个表的活动详细记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！