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

Мы уже многое сделали, чтобы наша реляционная модель соответствовала предметной области. Однако некоторые проблемы, нарушающие согласованность данных, остались. Например, мы можем добавить в таблицу 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);

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