To update rows using a view through which the table is accessed, observe the following rules:
- You must have the UPDATE privilege on the view.
The immediate owner of the view (that is, the containing database for the view) must have the UPDATE privilege on the underlying object (view, base table, or columns) whose columns are to updated, and the SELECT privilege on all tables that are specified in the WHERE clause.
- Each column of the view must correspond to a column in the underlying table (that is, none of the columns in the view can be derived using an expression).
- The data type definitions for an index column should match in the view definition and in the base table to which the view refers.
Although you can generally convert the data type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then the Optimizer does not use that index when the base table is updated because the data type of the recast column no longer matches the data type of the index column.
The resulting all-AMP, all-row scan defeats the performance advantages the index was designed for.
- No two view columns can reference the same table column.
- If the request used to define a view contains a WHERE clause WITH CHECK OPTION, then all values inserted through that view must satisfy the constraints specified in the WHERE clause.
- If a view includes a WHERE clause and does not specify WITH CHECK OPTION, then data can be inserted through the view that will not be visible through that view.