loading..
Русский    English
13:20
листать

Вложенные запросы в проверочных ограничениях

Мы уже многое сделали, чтобы наша реляционная модель соответствовала предметной области. Однако некоторые проблемы, нарушающие согласованность данных, остались. Например, мы можем добавить в таблицу PC модель (1288), которая в таблице Product объявлена как принтер:

  1. INSERT INTO PC VALUES(13, 1288, 500, 64, 10, '24x', 650);

Более того, ничто не мешает нам добавить эту модель во все продукционные таблицы – PC, Laptop, Printer.

Итак, нам требуется ограничение, которое бы запретило иметь в подчиненных таблицах продукты несоответствующего типа.

Сформулируем проверочное ограничение, которое будет определять тип модели по таблице Product и сравнивать его с типом продукционной таблицы. Например, для таблицы PC такое ограничение могло бы иметь вид:

  1. ALTER TABLE PC
  2. ADD CONSTRAINT model_type
  3. CHECK('PC' = (SELECT type FROM Product pr WHERE pr.model = pc.model));

При попытке выполнить вышеприведенный код (вполне легитимный с точки зрения стандарта SQL-92) получаем сообщение об ошибке:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

(Вложенные запросы в данном контексте запрещены. Допускаются только скалярные выражения.)

Другими словами, SQL Server не допускает использования подзапросов в ограничении CHECK. Что касается реализаций, то это, кстати, больше правило, чем исключение. Что касается MySQL, то эта СУБД вообще не поддерживает ограничений CHECK.

Восполнить этот пробел в SQL Server позволяет использование функций пользователя (UDF). Трюк состоит в следующем.

Поскольку, как это следует из сообщения об ошибке, в ограничении CHECK допускаются лишь скалярные выражения, напишем скалярнозначную функцию, которая будет принимать номер модели и возвращать ее тип, указанный в таблице Product. Затем эту функцию мы будем использовать в ограничении. Итак,

  1. CREATE FUNCTION get_type(@model VARCHAR(50))
  2. RETURNS VARCHAR(50)
  3. AS
  4. BEGIN
  5. RETURN (SELECT type FROM Product WHERE model=@model)
  6. END;

Теперь добавим ограничение:

  1. ALTER TABLE PC
  2. ADD CONSTRAINT model_type CHECK('PC' = dbo.get_type(model));

Теперь при попытке вставить в таблицу PC модель принтера, например:

  1. INSERT INTO PC VALUES(13, 1288, 500, 64, 10, '24x', 650);
мы получаем следующее сообщение об ошибке:

The INSERT statement conflicted with the CHECK constraint "model_type". The conflict occurred in database "learn", table "dbo.pc", column 'model'.

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

Модель же соответствующего типа можно добавить в таблицу:

  1. INSERT INTO PC VALUES(13, 1260, 500, 64, 10, '24x', 650);

Надеюсь, что вам не составит труда написать подобные ограничения и для остальных таблиц этой схемы.

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