Table Level Check Constraints
The schema description of the Painting database says there can’t be more than 255 units of paint of a single color on a single square. How can we implement such a constraint? The variants considered above won't work here, because any specific row of the utB table may meet all constraints for a single event of painting, but the total volume will exceed the permissible limit. Such a constraint is called a table-level constraint; when applied, it checks not just the row being changed but the whole table.
Since we again need a subquery in the CHECK constraint here (a feature not available in SQL Server), let’s write a user-defined function returning a 1 if the amount of paint of a specific color exceeds 255 on any square, and zero in the opposite case. The query underlying our UDF is fairly simple – grouping the records by square ID and color and subsequently filtering them by means of a HAVING clause on the criteria of the paint volume sum being greater than 255. If this query contains any rows the UDF returns 1, otherwise, it returns 0. Here is the function itself:
All that remains is to write a very simple constraint – the value returned by the function should be equal to zero (or not equal to 1 – just as you like):
Now, let’s try putting some paint on a white square (that is, a square dyed with the maximal possible amount of paint of every available color), e. g., on the square with b_q_id=1:
The following error message will be generated:
The INSERT statement conflicted with the CHECK constraint "square_volume". The conflict occurred in database "learn", table "dbo.utb". The statement has been terminated.
As an exercise, write a constraint forbidding the use of empty spray cans for painting (in other words, more than 255 units of paint from a single can).