Представления (VIEW) в MySQL - Обновляемость представлений

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

Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:

1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

Обратите внимание: встречающиеся в русско-язычной литературе требования, чтобы обновляемое представление было основано на единственной таблице и присутствие в числе полей представления первичного ключа физичекой таблицы не являются необходимыми. Скорее всего требование единственной таблицы является ошибкой перевода. Дело в том, что через представление, основанное на нескольких таблицах, может обновлять только одну таблицу за запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN, а не OUTER JOIN или UNION.

Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

При использовании в определении представления конструкции WITH [CASCADED / LOCAL] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

• Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
• Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.

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

Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:

• Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
• Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 2000.

punbb >CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)

punbb >SELECT * FROM v WHERE subject=`test`;
+------------+---------+-----------+
/ forum_name / subject / num_views /
+------------+---------+-----------+
/ Новости / test / 3000 /
+------------+---------+-----------+
1 row IN SET (0.03 sec)

punbb >UPDATE v SET num_views=2003 WHERE subject=`test`;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

punbb >SELECT * FROM v WHERE subject=`test`;
+------------+---------+-----------+
/ forum_name / subject / num_views /
+------------+---------+-----------+
/ Новости / test / 2003 /
+------------+---------+-----------+
1 row IN SET (0.01 sec)

punbb >SELECT subject, num_views FROM topics WHERE subject=`test`;
+---------+-----------+
/ subject / num_views /
+---------+-----------+
/ test / 2003 /
+---------+-----------+
1 rows IN SET (0.01 sec)

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

punbb >UPDATE v SET num_views=1999 WHERE subject=`test`;
ERROR 1369 (HY000): CHECK OPTION failed `punbb.v`

Не все обновляемые представления позволяют добавление данных:

punbb >INSERT INTO v (subject,num_views) VALUES(`test1`,4000);
ERROR 1369 (HY000): CHECK OPTION failed `punbb.v`

Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:

punbb >INSERT INTO v (forum_id,subject,num_views) VALUES(1,`test1`,4000);
ERROR 1054 (42S22): Unknown COLUMN `forum_id` IN `field list`

С другой строны:

punbb >INSERT INTO v (forum_name) VALUES(`TEST`);
Query OK, 1 row affected (0.00 sec)

Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.