Data manipulation through logical views

Previous Next

Data manipulation through Logical Views is limited:

1.You cannot INSERT, UPDATE or DELETE data through a Logical View if its SQL Statement contains one of the following:

the UNION ALL operator.

the DISTINCT function.

2.You can INSERT, UPDATE or DELETE data in a table T through a Logical View V only if the SELECT list in V's SQL Statement names ALL the primary key columns of T.
3.You cannot INSERT or UPDATE a view column that is subject to any SQL function in the view's SELECT list.
4.You cannot INSERT data through a Logical View that does not offer access to mandatory columns, unless those columns have default values.

The inability to perform data manipulation may take different shapes. An error message may appear. Inability to UPDATE may be signalled by non-updatable fields. Inability to DELETE may be tacit: if the Logical View joins two tables and one does not have all primary key columns named in the view's SQL, then DELETE may be performed only on the other table, without the user being signalled in any way about

If the primary key column(s) of more than one table appear in the SELECT list, manipulations (inserts, updates and deletes) are executed on each of these tables.

If your Logical View does not offer the data manipulation options you want, you can create these options by turning it into a rule-based logical view.

Example 1

In this Logical View, only the EMPLOYEE table has a primary key in the SELECT list. Therefore only the EMPLOYEE table is updatable through this Logical View:

,          e.dept_id
FROM       department d
,          employee e
WHERE      e.dept_id =

Example 2

In this Logical View, both underlying tables has a primary key in the SELECT list. A manipulation on this Logical View will be executed on both tables:

,          e.dept_id
FROM       department d
,          employee e
WHERE      e.dept_id =

Example 3

In this Logical View, you can UPDATE the FAMILY_NAME column but not the SALUTATION column, because the SALUTATION column is subject to SQL functions:

SELECT     p.person_id
,          p.family_name
,          'Dear ' ||
           ,  'M'
           ,  'Mr. '
           ,  'Ms. '
           ) ||
           UPPER( SUBSTR( p.family_name, 1, 1 ) ) ||
           LOWER( SUBSTR( p.family_name, 2 ) ||
           ': '
           ) salutation
FROM       person p


See also

How to define logical views

Checking logical views

Limitations of logical views

Logical view column attributes

Rule-based logical views