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;
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:
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.