Вставка и обновление с помощью представлений

Представление может использоваться для вставки или обновления. Однако существуют определенные ограничения.

Обновление с помощью представлений

Представление не может использоваться для обновления, если оно использует любой из следующих элементов:

• ALGORITHM=TEMPTABLE (см. Алгоритмы представлений)
• HAVING
• GROUP BY
• DISTINCT
• UNION
• UNION ALL
• Агрегатная функция, такая как MAX(), MIN(), SUM() или COUNT()
• Подзапрос в списке SELECT
• Подзапрос в предложении WHERE, ссылающийся на таблицу в предложении FROM
• Если оно не имеет базовой таблицы, поскольку ссылается только на литералы
• Предложение FROM содержит неудаляемое представление.
• Несколько ссылок на столбец любой базовой таблицы
• Внешнее соединение
• Внутреннее соединение, где более одной таблицы в определении представления обновляется
• Если есть предложение LIMIT, представление не содержит все столбцы первичного или не null уникального ключа из базовой таблицы, и переменная системы updatable_views_with_limit установлена в 0.

Вставка с помощью представлений

Представление не может использоваться для вставки, если оно не удовлетворяет критериям обновления, и должно также удовлетворять следующим условиям:

• Представление содержит все столбцы базовой таблицы, у которых нет значений по умолчанию
• Ни один столбец базовой таблицы не присутствует в списке выбора представления более одного раза
• Столбцы представления являются простыми столбцами и не выводятся каким-либо образом. Ниже приведены примеры выведенных столбцов
  • column_name + 25
  • LOWER(column_name)
  • (подзапрос)
  • 9.5
  • column1 / column2

Проверка возможности обновления представления

MariaDB сохраняет флаг IS_UPDATABLE для каждого представления, поэтому всегда можно проверить, считает ли MariaDB представление обновляемым (хотя и необязательно вставляемым) путем запроса столбца IS_UPDATABLE в таблице INFORMATION_SCHEMA.VIEWS.

WITH CHECK OPTION

Оператор WITH CHECK OPTION используется для предотвращения обновлений или вставок в представления, если условие WHERE в операторе SELECT не истинно.

Существует два ключевых слова, которые могут быть применены. WITH LOCAL CHECK OPTION ограничивает CHECK OPTION только определенным представлением, в то время как WITH CASCADED CHECK OPTION проверяет также все базовые представления. CASCADED считается значением по умолчанию, если ни одно ключевое слово не указано.

Если строка отклоняется из-за CHECK OPTION, генерируется ошибка, подобная следующей:

ERROR 1369 (HY000): CHECK OPTION failed 'db_name.view_name'

Представление с WHERE, которое всегда ложно (например, WHERE 0) и WITH CHECK OPTION аналогично таблице BLACKHOLE: ни одна строка никогда не вставляется и ни одна строка никогда не возвращается. Вставляемое представление с WHERE, которое всегда ложно, но без CHECK OPTION, — это представление, которое принимает данные, но не отображает их.

Примеры

CREATE TABLE table1 (x INT);

CREATE VIEW view1 AS SELECT x, 99 AS y FROM table1;

Проверка возможности обновления представления:

SELECT TABLE_NAME,IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS;
+------------+--------------+
| TABLE_NAME | IS_UPDATABLE |
+------------+--------------+
| view1      | YES          |
+------------+--------------+

Этот запрос работает, так как представление обновляется:

UPDATE view1 SET x = 5;

Этот запрос терпит неудачу, так как столбец y является литералом.

UPDATE view1 SET y = 5;
ERROR 1348 (HY000): Column 'y' is not updatable

Ниже приведены три представления для демонстрации оператора WITH CHECK OPTION.

CREATE VIEW view_check1 AS SELECT * FROM table1 WHERE x < 100 WITH CHECK OPTION;

CREATE VIEW view_check2 AS SELECT * FROM view_check1 WHERE x > 10 WITH LOCAL CHECK OPTION;

CREATE VIEW view_check3 AS SELECT * FROM view_check1 WHERE x > 10 WITH CASCADED CHECK OPTION;

Эта вставка выполняется успешно, так как view_check2 проверяет вставку только относительно view_check2, и условие WHERE оценивается как истинное (150 — >10).

INSERT INTO view_check2 VALUES (150);

Эта вставка терпит неудачу, так как view_check3 проверяет вставку относительно как view_check3, так и базовых представлений. Условие WHERE для view_check1 оценивается как ложное (150 — >10, но 150 не <100), поэтому вставка терпит неудачу.

INSERT INTO view_check3 VALUES (150);
ERROR 1369 (HY000): CHECK OPTION failed 'test.view_check3'