loading..
Русский    English
15:04

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:

  1. CREATE FUNCTION check_volume()
  2. RETURNS INT
  3. AS
  4. BEGIN
  5. DECLARE @ret int
  6. IF EXISTS(SELECT SUM(B_VOL)
  7. FROM utB JOIN utV ON b_v_id=v_id
  8. GROUP BY b_q_id, V_COLOR
  9. HAVING SUM(B_VOL) > 255)
  10. SELECT @ret =1 ELSE SELECT @ret = 0;
  11. RETURN @ret;
  12. 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):

  1. ALTER TABLE utB
  2. 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:

  1. INSERT INTO utB VALUES(CURRENT_TIMESTAMP, 1, 4, 10);

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

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100