Custom function with check constraint SQL Server 2008

Question

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

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.

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.

Answer 1

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