To delete table rows using a view through which the table is accessed, refer to the following conditions.
- You must have the DELETE privilege on the view. Also, the immediate owner of the view (that is, the database in which the view resides) must have the DELETE privilege on the underlying object (view or base table) whose rows are to be deleted, 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 both the view definition and in the base table to which the view refers.
While it is true that you can generally convert the type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then that index is not used to delete rows from the base table because the data type of the recast column no longer matches the data type definition for that column in the index.
The resulting all-AMP, all-row behavior of the delete circumvents the performance advantages for which the index was designed.
- Any two view columns cannot reference the same table column.
- The view cannot include a column that contains a range constraint.
- The expression used to define a view cannot have a data type specified for any column in the view.