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
An inherently updatable view cannot be defined with a query that has:
A set operation (
Aggregation (aggregate functions,
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:
INSERT/UPDATEcan only modify a single key-preserved table.
DELETEoperations, 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,
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.
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.