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:

CREATE FUNCTION check_volume()
RETURNS INT
AS
BEGIN
DECLARE @ret int
if EXISTS(SELECT sum(B_VOL)
FROM utB join utV on b_v_id=v_id
group by b_q_id, V_COLOR
HAVING sum(B_VOL) > 255)
SELECT @ret =1 else SELECT @ret = 0;
RETURN @ret;
END;

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

Alter table utB
ADD CONSTRAINT square_volume check(dbo.check_volume() = 0);

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:

insert into utB values(CURRENT_TIMESTAMP, 1, 4, 10);

The following error message will be generated:

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