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

Answer 2

You could use a scalar UDF for this but using those in check constraints has well documented problems (e.g. see Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates or Snapshot isolation: A threat for integrity? series.

It is also possible to get the database engine to enforce this with an indexed view

It needs a helper table with 2 rows as CTEs and UNION are not allowed in indexed views. The idea is that the view definition should always return zero rows and this doubles any rows that are returned if there is a violation. Therefore causing a breach of the unique constraint on the view and causing an error to occur and the statement to fail.

CREATE TABLE dbo.TwoNums
  (
     Num INT PRIMARY KEY
  )

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

Then the view definition

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 

And a unique index on the View

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

The reason for including all four columns is so that all the required values are shown in the error message.

Cannot insert duplicate key row in object 'dbo.PreventOverCapacity' with unique index 'UIX_PreventOverCapacity (Venueid, EventId, Expected, Capacity)'.
The duplicate key value is (1, 97, 110, 100).