带有检查约束的自定义函数 SQL Server 2008 [英] Custom function with check constraint SQL Server 2008

问题描述

我使用 SQL Server 2008 并且我有两个现有表，venues 和 events.

I working with SQL Server 2008 and I have two existing tables, venues and events.

我正在尝试创建一个带有检查约束的自定义函数，以确保 events 表中 event_expected_attendance 列中的整数始终小于或等于venues 表中的 venue_max_capacity 整数.

I am trying to create a custom function with a check constraint to make sure that the integer in the event_expected_attendance column in the events table is always less than or equal to the venue_max_capacity integer in the venues table.

由于检查约束位于两个表之间，因此我在处理自定义函数的语法和 join 语句时遇到了困难.

I am struggling with the syntax of a custom function as well as the join statement since the check constraint is between two tables.

感谢您的帮助！我会尽快回答任何其他问题.

Thanks for the help! I will answer any additional questions quickly.

推荐答案

正如 Martin Smith 所说，使用带有 UDF 的检查约束存在一些问题，并且可能会对性能产生负面影响，但是如果您无论如何都想尝试它代码应该可以工作:

As stated by Martin Smith using a check constraint with a UDF has some problems and might have a negative impact on performance, but if you want to try it anyway this code should work:

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); 

这篇关于带有检查约束的自定义函数 SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！