Представления обеспечивают выполнение сложных проверок целостности

Рассмотрим схему для сети магазинов, содержащую три таблицы:

CREATE TABLE Stores

(store_nbr INTEGER NOT NULL PRIMARY KEY,

store_name CHAR(35) NOT NULL,

...);

CREATE TABLE Personnel

(ssn CHAR(9) NOT NULL PRIMARY KEY,

last_name CHAR(15) NOT NULL,

first_name CHAR(15) NOT NULL,

...);

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

CREATE TABLE JobAssignments

(store_nbr INTEGER NOT NULL REFERENCES Stores (store_nbr)

ON UPDATE CASCADE

ON DELETE CASCADE,

ssn CHAR(9) NOT NULL PRIMARY KEY REFERENCES Personnel(ssn)

ON UPDATE CASCADE

ON DELETE CASCADE,

start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

end_date TIMESTAMP CHECK (start_date <= end_date),

job_type INTEGER DEFAULT 0 NOT NULL,

CHECK (jobjtype BETWEEN 0 AND 99),

PRIMARY KEY (store_nbr, ssn, start_date));

Допустим, что код должности job_type = 0 соответствует значению “не назначено”, job_type = 1 соответствует ученику помощника грузчика, и так далее, пока не доберемся до кода 99 — управляющий магазином. Понятно, что управляющий в каждом магазине только один. В стандарте SQL-92 это ограничение записывается так:

CHECK (NOT EXISTS

(SELECT store_nbr

FROM JobAssignments

WHERE job_type = 99))

 

GROUP BY store_nbr HAVING COUNT(*) > 1))

Но во многих SQL-продуктах не допускается применять ограничение СНЕСК() к таблице в целом, и ограничение CREATE ASSERTION на уровне схемы они не поддерживают. Как же быть? Воспользоваться триггером? Но ведь для этого понадобится — о, ужас! — процедурная программа! Несмотря на наличие SQL/PSM и других стандартов, большинство разработчиков реализуют различные модели триггеров с собственными 4GL-языками, но я — фанатик и хочу чистого SQL-решения.
Создадим две таблицы:

CREATE TABLE Job_99_Assignments

(store_nbr INTEGER NOT NULL PRIMARY KEY REFERENCES Stores (store_nbr)

ON UPDATE CASCADE

ON DELETE CASCADE,

ssn CHAR(9) NOT NULL REFERENCES Personnel (ssn)

ON UPDATE CASCADE

ON DELETE CASCADE,

start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

end_date TIMESTAMP CHECK (start_date <= end_date),

job_type INTEGER DEFAULT 99 NOT NULL CHECK (job_type = 99));

CREATE TABLE Job_not99_Assignments

(store_nbr INTEGER NOT NULL REFERENCES Stores (store_nbr)

ON UPDATE CASCADE

ON DELETE CASCADE,

ssn CHAR(9) NOT NULL PRIMARY KEY REFERENCES Personnel(ssn)

ON UPDATE CASCADE

ON DELETE CASCADE,

start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

end_date TIMESTAMP CHECK (start_date <= end_date),

job_type INTEGER DEFAULT 0 NOT NULL

CHECK (job_type BETWEEN О AND 98) -- кода 99 быть не должно

);

Теперь создадим представление с помощью UNION:

CREATE VIEW JobAssignments (store_nbr, ssn, start_date, end_date, job_type)

AS

(SELECT store_nbr, ssn, start_date, end_date, job_type

FROM Job_not99_Assignments

UNION ALL

SELECT store_nbr, ssn, start_date, end_date, job_type

FROM Job_99_Assignments)

Совместное действие ключей и ограничений на значение столбца job_type в обеих таблицах гарантирует, что в каждом магазине будет только один управляющий. Далее нужно будет добавить в описание представления триггер INSTEAD OF, чтобы пользователи могли вставлять, обновлять и удалять данные.
Упражнение для читателя: как организовать проверку того, что у каждого управляющего не более двух помощников?

Обновляемые представления

Стандарт SQL-92, по правде сказать, весьма консервативен в отношении того, какие представления можно обновлять. Обновляемое представление должно удовлетворять следующим требованиям.
1. Представление должно иметь в основе оператор SELECT, основанный на одной и только одной таблице. Впрочем, допускается многоуровневое создание представления на основе других представлений.
2. Представление должно включать все столбцы, связанные ограничениями UNIQUE или PRIMARY KEY. Это гарантирует, что каждая строка представления соответствует одной и только одной строке базовой таблицы.
3. Всем столбцам базовой таблицы, отсутствующим в представлении,должно быть присвоено значение по умолчанию, или же эти столбцы должны допускать значение NULL Причина очевидна: когда высоздаете в представлении новую строку, вставляя ее в базовую таблицу, система должна знать, чем заполнять столбцы, которых нет впредставлении.
В реальных продуктах обновлять допускается и другие представления, но в любом случае за обновлением всегда скрываются правила вставки, обновления и удаления строк, которые связывали бы строки представления со строками базовой таблицы.