Using Updatable Views

In UPDATE, INSERT and DELETE statements, NuoDB supports specification of updatable views in place of tables. Depending on the specific statement, it is possible to update the underlying table of a view by referring to the view in the SQL DML statement. You must have update privileges on both the underlying table and the view that the DML operation is being executed.

There are restrictions on views that are updatable. For instance, every row that exists in an updatable view must be directly related to a row in the underlying table. For this reason, the definition of an updatable view cannot include the following:

Columns that are excluded from the view definition must either allow NULL values or have a default value defined for the column in the underlying table. Otherwise an INSERT or UPDATE SQL DML statement on the view will return an error.

An UPDATE SQL DML statement can remove rows from the result set defined by a view, but this does not remove the rows from the underlying table (see Removing a Row from the View But Not the Table). For example, the UPDATE causes the row to no longer be included in the view criteria. A subsequent UPDATE or DELETE using the view will no longer change that row in the underlying table.

An UPDATE or DELETE SQL DML statement can only change rows in the underlying table if they exist in the result set defined by the view (see Removing a Row from a View And the Table). For example, a view is defined with criteria that includes only columns with a status of "Active". A DELETE SQL DML statement executed using the view to remove all rows in the underlying table that have a delete_flag of "True" will not remove rows from the underlying table that meet this criteria if the status does not equal "Active" since only rows with a status of "Active" are included in the view result set.

Constraint checking, as specified by the underlying table definition, will be enforced when inserting a new row or updating an existing row when using a view in the DML SQL statement. Examples of this constraint checking include: