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

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

insert into PC values(13, 1288, 500, 64, 10, '24x', 650);

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

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

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

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

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

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

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

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

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

CREATE FUNCTION get_type(@model VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
RETURN (SELECT type FROM Product WHERE model=@model)
END;

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

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

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

insert into PC values(13, 1288, 500, 64, 10, '24x', 650);

мы получаем следующее сообщение об ошибке:

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

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

insert into PC values(13, 1260, 500, 64, 10, '24x', 650);

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