SQL Server 2008中带检查约束的自定义函数

Question

我正在使用SQL Server 2008，并且我有两个现有的表，`venues`和`events`。

我正在尝试创建一个自定义函数与检查约束来确保`events`表中`event_expected_attendance`列中的整数始终小于或等于`venues`表中的`venue_max_capacity`整数。

由于检查约束位于两个表之间，因此我遇到了自定义函数和连接语句的语法问题。

谢谢帮助！我会快速回答任何其他问题。

- James

---

是的，这两个表将根据 event_id = event_venue_id 进行连接。 - James

一个触发器也可以实现这个功能，但它会影响插入/更新/删除的性能。你可以在events表上构建一个触发器，检查venues表并引发错误/回滚无效值。 - Nick.McDermaid

Answer 1

正如Martin Smith所述，使用带有UDF的检查约束存在一些问题，并可能对性能产生负面影响，但如果您仍想尝试，请使用以下代码：

CREATE FUNCTION dbo.CheckVenueCapacity (@venue_id int, @capacity int)
RETURNS int
AS 
BEGIN
  DECLARE @retval int
    SELECT @retval = CASE WHEN venue_max_capacity >= @capacity THEN 0 ELSE 1 END
    FROM venues
    WHERE venue_id = @venue_id 
  RETURN @retval
END;
GO

ALTER TABLE events 
  ADD CONSTRAINT chkVenueCapacity 
  CHECK (dbo.CheckVenueCapacity(event_venue_id, event_expected_attendance) = 0); 

- jpw

Answer 2

你可以使用标量UDF来实现，但在检查约束中使用这些函数已有充分的文档说明问题（例如，参见包装在CHECK约束中的标量UDF非常慢，可能在多行更新时失败或快照隔离：对完整性的威胁？系列）。

还可以通过创建一个索引视图来让数据库引擎来执行这个约束。

它需要一个帮助表格，其中有两行，因为CTE和UNION在索引视图中是不被允许的。这个视图定义总是返回零行，如果有违反唯一约束条件的情况，则会将返回的任何行都加倍。从而导致视图上的唯一约束出现违规，并导致发生错误并使语句失败。

CREATE TABLE dbo.TwoNums
  (
     Num INT PRIMARY KEY
  )

INSERT INTO dbo.TwoNums
VALUES      (1),
            (2) 
 

然后是视图定义。

CREATE VIEW dbo.PreventOverCapacity
WITH SCHEMABINDING
AS
  SELECT V.Venueid,
         E.EventId,
         E.Expected,
         V.Capacity
  FROM   dbo.Venues V
         JOIN dbo.Events E
           ON E.venueid = V.venueid
              AND E.Expected > V.Capacity
         CROSS JOIN dbo.TwoNums 

在视图上创建唯一索引

CREATE UNIQUE CLUSTERED INDEX [UIX_PreventOverCapacity (Venueid, EventId, Expected, Capacity)]
  ON dbo.PreventOverCapacity(Venueid, EventId, Expected, Capacity) 
    

包含所有四列的原因是为了在错误信息中显示所有所需的值。

无法将重复键行插入到对象'dbo.PreventOverCapacity'中，因为它具有唯一索引'UIX_PreventOverCapacity (Venueid, EventId, Expected, Capacity)'。
重复键值为(1, 97, 110, 100)。

- Martin Smith

---

第一个链接已经有11年的历史了，而且给出的SQL甚至无法正确编译。第二个链接则更老，我不认为任何人应该使用这样古老的建议来决定在2020年是否使用UDF作为检查约束的问题。 - Ian Kemp

@IanKemp，如果你这样认为，那么你就错了。问题完全相同。它们仍然是逐行评估的，因此中间状态违反约束条件可能会失败，尽管语句结束时的状态不会，而且仍然需要考虑快照隔离并添加适当的锁提示。使用索引视图意味着您可以声明性地定义它，并利用正确编写的产品功能来避免这些陷阱。 - Martin Smith