이 콘텐츠는 선택한 언어로 제공되지 않습니다.
Chapter 5. Updatable Views
Any view can be marked as updatable. In many circumstances the view definition allows the view to be inherently updatable without the need to manually define a trigger to handle INSERT/UPDATE/DELETE
operations.
An inherently updatable view cannot be defined with a query that has:
-
A set operation (
INTERSECT
,EXCEPT
,UNION
). -
SELECT DISTINCT
. -
Aggregation (aggregate functions,
GROUP BY
,HAVING
). -
A
LIMIT
clause.
A UNION ALL
can define an inherently updatable view only if each of the UNION branches are themselves inherently updatable. A view defined by a UNION ALL
can accommodate inherent INSERT
statements if it is a partitioned union, and the INSERT
specifies values that belong to a single partition. For more information, see partitioned union in Federated optimizations.
Any view column that is not mapped directly to a column is not updatable and cannot be targeted by an UPDATE set clause or be an INSERT column.
If a view is defined by a join query or has a WITH
clause it might still be inherently updatable. However, in these situations there are further restrictions, and the resulting query plan may execute multiple statements. For a non-simple query to be updatable, the following criteria apply:
-
An
INSERT/UPDATE
can only modify a single key-preserved table. To allow
DELETE
operations, there must be only a single key-preserved table.For information about key-preserved tables, see Key-preserved tables.
If the default handling is not available or if you want to have an alternative implementation of an INSERT/UPDATE/DELETE
, you can use update procedures, or triggers, to define procedures to handle the respective operations. For more information see Update procedures (Triggers).
Consider the following example of an inherently updatable denormalized view:
create foreign table parent_table (pk_col integer primary key, name string) options (updatable true); create foreign table child_table (pk_col integer primary key, name string, fk_col integer, foreign key (fk_col) references parent_table (pk_col)) options (updatable true); create view denormalized options (updatable true) as select c.fk_col, c.name as child_name, p.name from parent_table as p, child_table as c where p.pk_col = c.fk_col;
A query such as insert into denormalized (fk_col, child_name) values (1, 'a')
would succeed against this view, because it targets a single key-preserved table, child_table
. However, insert into denormalized (name) values ('a')
would fail, because it maps to a parent_table
that can have multiple rows for each parent_table
key. In other words, it is not key-preserved.
Also, an INSERT
against parent_table
alone might not be visible to the view, because there might be no child entities associated either.
Not all scenarios will work. Referencing the preceding example, an insert into denormalized (pk_col, child_name) values (1, 'a')
with a view that is defined using the p.pk_col
will fail, because the logic doesn’t yet consider the equivalency of the key values.
5.1. Key-preserved tables
A key-preserved table has a primary or unique key that remains unique when it is projected into the result of the query. Note that it is not actually required for a view to reference the key columns in the SELECT clause. The query engine can detect a key-preserved table by analyzing the join structure. The engine will ensure that a join of a key-preserved table must be against one of its foreign keys.