基于另一列的内容强制列的唯一性

Question

我有典型的Invoice/InvoiceItems主/详细表(每本书和教程都使用这些表作为示例)。我也有一个"形式"表，其中保存的数据类似于发票，有时链接到发票。两者都链接到发票中的每个项目，其中一列可选地引用形式表，如下所示:

id | id_invoice | id_proforma | amount ....... and a bunch of irrelevant stuff
-----------------------------------------------
1  | 1          | null        | 100
2  | 1          | null        | 40
3  | 2          | 3           | 1000
4  | 3          | 4           | 473
5  | 3          | 4           | 139

基本上，发票中的每个项都可以链接到形式表。还有一条业务规则规定，每种形式报表只能在一张发票中使用(在同一发票中的许多项目中使用它是可以的)。

目前该规则是在应用程序端强制执行的，但这有并发性问题，因为2个用户可以同时使用相同的形式，而系统会让它通过。我的意图是让DB验证这一点，以及一些前端视觉线索，但到目前为止，我还没有针对这种特殊情况提出一种方法。

过滤的唯一索引可以很好地服务，除了相同的形式可以使用两次，如果它是相同的发票，所以我的问题是，我如何使DB服务器执行该规则?

数据库引擎可以是SQL 2012或更高版本，也可以是从express到enterprise的任意版本。

Answer 1

可以创建一个用户定义的标量函数，如果形式发票id和发票id组合有效，则该函数返回TRUE。然后在表上放置一个检查约束，要求函数返回true。像这样(调整以适应您的表名/需要):

-- Here's the function:
create function dbo.svfIsCombinationValid (
                                           @id_invoice  int
                                         , @id_proforma int
                                          )
returns bit
as
begin;
   declare @return bit = 1;
   if exists (
              select 1
                from dbo.YourInvoiceProformaXRefTable
               where id_proforma = @id_proforma
                 and id_invoice <> @id_invoice
             )
   begin;
      set @return = 0;
   end;
   return @return;
end;

之后，您可以修改表并添加检查约束:

alter table dbo.YourInvoiceProformaXRefTable
add constraint CK_YourInvoiceProformaXRefTable_UniqueInvoiceProforma
check (dbo.svfIsCombinationValid(id_invoice,id_proforma)=1);

这是OK的空(多个id_invoice可以有id_forma NULL值)。但如果两个值都不为空，则组合必须为NEW或与现有行相同。