loading..
Русский    English
09:31
листать

Проверочное ограничение уровня таблицы

В описании схемы «Окраска»  утверждается, что объем краски одного цвета на квадрате не может превышать 255 единиц. Как реализовать это ограничение? Рассмотренные ранее варианты нам не подойдут, т.к. каждая строка таблицы utB может отвечать всем ограничениям на отдельную окраску, но суммарный объем при этом может превысить допустимый предел. Ограничение подобного типа называется ограничением уровня таблицы, т.е. при проверке оно адресуется не к отдельной строке, которой коснулось изменение, а ко всей таблице.

Поскольку тут нам опять потребуется запрос в ограничении CHECK, что не реализовано, напишем сначала пользовательскую функцию, которая будет возвращать 1, если объем какой-либо краски на каком-либо квадрате превысил 255 единиц, и ноль – в противном случае. Лежащий в основе UDF запрос достаточно прост – группировка по ИД квадрата и цвету с фильтрацией в предложении HAVING по условию, что сумма краски превысила 255. Если такой запрос будет содержать строки, то функция вернет 1, иначе – 0. Собственно функция:

  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;

Осталось написать совсем простое ограничение – возвращаемое функцией значение равно 0 или не равно 1, - это кому как нравится:

  1. ALTER TABLE utB
  2. ADD CONSTRAINT square_volume CHECK(dbo.check_volume() = 0);

Попробуем теперь добавить какой-нибудь краски к белому квадрату (т.е. квадрату, который уже окрашен по максимуму всеми цветами), например, квадрату с b_q_id=1:

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

В результате мы получим ошибку:

The INSERT statement conflicted with the CHECK constraint "square_volume". The conflict occurred in database "learn", table "dbo.utb". The statement has been terminated.

(Конфликт инструкции INSERT с ограничением CHECK "square_volume". Конфликт произошел в базе данных "learn", таблица "dbo.utb". Выполнение данной инструкции было прервано.)

В качестве упражнения напишите ограничение, которое запретит использование пустых баллончиков, т.е. когда объем краски, израсходованной из баллончика, оказывается более 255.


Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100