В представлении хранится итог по отделам. Объясните, почему стандартный SQL не рассматривает такое представление как автоматически обновляемое при выполнении INSERT.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
department_id INTEGER NOT NULL,
salary INTEGER NOT NULL
);
CREATE VIEW department_totals AS
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
INSERT INTO department_totals VALUES (10, 500000);
Представление с GROUP BY и SUM не является автоматически обновляемым, потому что его строка представляет результат объединения нескольких строк базовой таблицы, а не одну однозначно определяемую строку employees. СУБД не может по значениям department_id и total_salary однозначно вывести, какую строку сотрудников создать или изменить и как распределить сумму по сотрудникам.
Операция обычно завершается ошибкой о невозможности изменения представления. Для изменения данных нужно обращаться к базовой таблице либо заранее определить отдельную бизнес-операцию, например добавление нового сотрудника с конкретной зарплатой.
Представления появились как средство скрыть физическую структуру данных и предоставить пользователю логический интерфейс к отношениям. Они поддерживают абстракцию, повторное использование запросов и ограничение доступа к отдельным строкам или столбцам.
Однако не всякое представление сохраняет однозначное соответствие между своими строками и строками базовых таблиц. Агрегация, группировка и другие преобразования разрушают такую однозначность, поэтому стандарт SQL различает представления, которые могут быть изменяемыми, и представления, доступные только для чтения.
В исходной таблице каждая строка соответствует одному сотруднику. В представлении одна строка соответствует целому отделу, а total_salary является вычисленным итогом по нескольким сотрудникам.
Для команды INSERT INTO department_totals VALUES (10, 500000) не определено, нужно ли создать одного сотрудника с зарплатой 500000, несколько сотрудников с произвольным распределением зарплат или изменить уже существующих сотрудников. Любой такой выбор добавлял бы семантику, которой нет в запросе.
Автоматическое обновление представления требует, чтобы операция над строкой представления могла быть однозначно преобразована в операцию над базовой таблицей. В данном случае GROUP BY превращает множество строк employees в одну строку результата, а SUM вычисляет производное значение.
У представления нет отдельного столбца, который указывал бы, какую строку сотрудника изменять. Более того, изменение total_salary требует выбрать способ распределения разницы между сотрудниками отдела. Поэтому стандарт не обязан принимать такую вставку, обновление или удаление как автоматически преобразуемую операцию.
После вставки агрегатное представление пересчитывается при чтении. Важно, что вставляется не строка итогового представления, а конкретная строка базовой таблицы; связь между действием и изменением данных однозначна.
Простое представление без группировки иногда может быть изменяемым, но это зависит от структуры запроса и ограничений стандарта. Наличие вычисляемых столбцов, DISTINCT, агрегатных функций, GROUP BY, операций над множествами или некоторых соединений может сделать представление непотенциально изменяемым. Конкретная СУБД также может иметь дополнительные правила и расширения.
Отчёт по фонду оплаты труда был опубликован как department_totals, после чего приложение попыталось использовать его для добавления бюджета отдела. Вариант с прямой вставкой в представление отклонили: итоговая сумма не определяет состав сотрудников и не должна подменять кадровую операцию.
Рассматривались два решения. Первое — вставлять сотрудников непосредственно в employees; это прозрачно и сохраняет нормальную модель данных, но требует от клиента знать структуру базовой таблицы. Второе — создать хранимую процедуру add_employee, принимающую идентификатор, отдел и зарплату; это лучше инкапсулирует правила приложения, но добавляет отдельный программный интерфейс.
Выбрали процедуру для бизнес-операций, а представление оставили только для чтения отчётов. В результате агрегаты автоматически отражают изменения базовых данных, а распределение зарплаты не определяется неявными правилами СУБД.
Вопрос: Можно ли обновить через это представление столбец department_id, не изменяя total_salary?
Ответ: Нет, сама проблема не ограничивается конкретным столбцом. Строка представления всё равно является результатом группы, а не строкой базовой таблицы. Изменение идентификатора группы потребовало бы перераспределить несколько исходных строк между группами, что не является однозначной операцией.
Вопрос: Почему добавление WITH CHECK OPTION не делает агрегатное представление изменяемым?
Ответ: WITH CHECK OPTION проверяет, что изменённая строка остаётся видимой через предикат представления. Эта опция не определяет, как преобразовать строку агрегированного результата в строки базовой таблицы и не устраняет неоднозначность SUM и GROUP BY.
Вопрос: Почему представление с фильтром иногда можно обновлять, хотя агрегатное — нельзя?
Ответ: В представлении вида SELECT employee_id, department_id, salary FROM employees WHERE department_id = 10 строка результата обычно сохраняет однозначное соответствие строке employees. СУБД может изменить базовую строку и затем проверить, продолжает ли она удовлетворять фильтру. В агрегатном представлении такая построчная связь утрачена, поскольку одна строка результата зависит от множества исходных строк.