Oracle7 Server Application Developer's Guide

Modifying a Join View

The Oracle Server for release 7.3 allows you, with some restrictions, to modify views that involve joins.

In previous releases of the Oracle Server, you could not issue UPDATE, INSERT, or DELETE statements against a join view. Consider the following simple view:

CREATE VIEW emp_view AS
  SELECT ename, empno, deptno FROM emp;

This view does not involve a join operation. If you issue the SQL statement:

UPDATE emp_view SET ename = 'CAESAR' WHERE empno = 7839;

then the EMP base table that underlies the view changes, and employee 7839's name changes from KING to CAESAR in the EMP table.

However, if you create a view such as

CREATE VIEW emp_dept AS
  SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
    FROM emp e, dept d    /* JOIN operation */
    WHERE e.deptno = d.deptno
      AND d.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

then in Oracle Server releases prior to 7.3 you could not modify either the EMP or the DEPT base table through this view, because it involves a join operation. A statement such as

UPDATE emp_dept_view SET ename = 'JOHNSON' 
    WHERE ename = 'SMITH';

would have failed with an ORA-01732 error.

ORA-01732: "data manipulation operations not legal on this view"

Oracle7 release 7.3 allows you to modify such a view, subject to the restrictions described below.

A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following:

DISTINCT operator

aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE

set operations: UNION, UNION ALL, INTERSECT, MINUS

GROUP BY or HAVING clauses

START WITH or CONNECT BY clauses

ROWNUM pseudocolumn

A further restriction on which join views are modifiable is that if a view is a join on other nested views, then the other nested views must be mergeable into the top level view. See Chapter 5 in the Oracle7 Server Tuning manual for more information about mergeable views.

Example Tables

The examples in this section use the familiar EMP and DEPT tables. However, the examples work only if you explicitly define the primary and foreign keys in these tables, or define unique indexes. Here are the appropriately constrained table definitions for EMP and DEPT:

CREATE TABLE dept (
        deptno   NUMBER(4) PRIMARY KEY,
        dname    VARCHAR2(14),
        loc      VARCHAR2(13));

CREATE TABLE emp (
        empno    NUMBER(4) PRIMARY KEY,
        ename    VARCHAR2(10),
        job      varchar2(9),
        mgr      NUMBER(4),
        hiredate DATE,
        sal      NUMBER(7,2),
        comm     NUMBER(7,2),
        deptno   NUMBER(2),
        FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));.

You could also omit the primary and foreign key constraints listed above, and create a UNIQUE INDEX on DEPT (DEPTNO) to make the following examples work.

Key-Preserved Tables

The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

Note: It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join.

Attention: The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema and not of the data in the table. For example, if in the EMP table there was at most one employee in each department, then DEPT.DEPTNO would be unique in the result of a join of EMP and DEPT, but DEPT would still not be a key-preserved table.

If you SELECT all rows from EMP_DEPT_VIEW defined above, the results are

EMPNO      ENAME      DEPTNO     DNAME          LOC 
---------- ---------- ---------- -------------- -----
      7782 CLARK              10 ACCOUNTING     NEW YORK
      7839 KING               10 ACCOUNTING     NEW YORK
      7934 MILLER             10 ACCOUNTING     NEW YORK
      7369 SMITH              20 RESEARCH       DALLAS
      7876 ADAMS              20 RESEARCH       DALLAS
      7902 FORD               20 RESEARCH       DALLAS
      7788 SCOTT              20 RESEARCH       DALLAS
      7566 JONES              20 RESEARCH       DALLAS
8 rows selected.

In this view, EMP is a key-preserved table, because EMPNO is a key of the EMP table, and also a key of the result of the join. DEPT is not a key-preserved table, because although DEPTNO is a key of the DEPT table, it is not a key of the join.

Rule for DML Statements on Join Views

Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table.

UPDATE Statements

The following example shows an UPDATE statement that successfully modifies the EMP_DEPT view (shown

UPDATE emp_dept
  SET sal = sal * 1.10 
    WHERE deptno = 10;

The following UPDATE statement would be disallowed on the EMP_DEPT view:

UPDATE emp_dept
  SET loc = 'BOSTON'
    WHERE ename = 'SMITH';

This statement fails with an ORA-01779 error (``cannot modify a column which maps to a non key-preserved table''), because it attempts to modify the underlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view.

In general, all modifiable columns of a join view must map to columns of a key-preserved table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable.

So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, the following UPDATE statement would fail:

UPDATE emp_dept
  SET deptno = 10
    WHERE ename = 'SMITH';

The statement fails because it is trying to update a join column.

DELETE Statements

You can delete from a join view provided there is one and only one key-preserved table in the join.

The following DELETE statement works on the EMP_DEPT view:

DELETE FROM emp_dept
  WHERE ename = 'SMITH';

This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE operation on the base EMP table, and because the EMP table is the only key-preserved table in the join.

In the following view, a DELETE operation cannot be performed on the view because both E1 and E2 are key-preserved tables:

CREATE VIEW emp_emp AS
  SELECT e1.ename, e2.empno, deptno
    FROM emp e1, emp e2
    WHERE e1.empno = e2.empno;

If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. For example:

CREATE VIEW emp_mgr AS
  SELECT e1.ename, e2.ename mname
    FROM emp e1, emp e2
      WHERE e1.mgr = e2.empno
      WITH CHECK OPTION;

No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.

INSERT Statements

The following INSERT statement on the EMP_DEPT view succeeds:

INSERT INTO emp_dept (ename, empno, deptno)
  VALUES ('KURODA', 9010, 40);

because only one key-preserved base table is being modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the FOREIGN KEY integrity constraint on the EMP table).

An INSERT statement such as

INSERT INTO emp_dept (ename, empno, deptno)
  VALUES ('KURODA', 9010, 77);

would fail for the same reason that such an UPDATE on the base EMP table would fail: the FOREIGN KEY integrity constraint on the EMP table is violated.

An INSERT statement such as

INSERT INTO emp_dept (empno, ename, loc)
  VALUES (9010, 'KURODA', 'BOSTON');

would fail with an ORA-01776 error (``cannot modify more than one base table through a view'').

An INSERT cannot, implicitly or explicitly, refer to columns of a non-key-preserved table. If the join view is defined using the WITH CHECK OPTION clause, then you cannot perform an INSERT to it.

Using the UPDATABLE_COLUMNS Views

To assist you in using the capability of modifying join views, three new views have been created in Oracle7. These views are defined in Table 4 - 1.

View Name Description
USER_UPDATABLE_COLUMNS Shows all columns in all tables and views in the user's schema that are modifiable.
DBA_UPDATABLE_COLUMNS Shows all columns in all tables and views in the DBA schema that are modifiable.
ALL_UPDATABLE_VIEWS Shows all columns in all tables and views that are modifiable.

Table 4 - 1. UPDATABLE_COLUMNS Views

Outer Joins

Views that involve outer joins are modifiable in some cases. For example, the following view:

CREATE VIEW emp_dept_oj1 AS
    SELECT empno, ename, e.deptno, dname, loc
    FROM emp e, dept d
    WHERE e.deptno = d.deptno (+);

The statement

SELECT * FROM emp_dept_oj1;

results in:

EMPNO   ENAME      DEPTNO  DNAME          LOC          
------- ---------- ------- -------------- -------------
   7369 SMITH           40 OPERATIONS     BOSTON       
   7499 ALLEN           30 SALES          CHICAGO      
   7566 JONES           20 RESEARCH       DALLAS       
   7654 MARTIN          30 SALES          CHICAGO      
   7698 BLAKE           30 SALES          CHICAGO      
   7782 CLARK           10 ACCOUNTING     NEW YORK     
   7788 SCOTT           20 RESEARCH       DALLAS       
   7839 KING            10 ACCOUNTING     NEW YORK     
   7844 TURNER          30 SALES          CHICAGO      
   7876 ADAMS           20 RESEARCH       DALLAS       
   7900 JAMES           30 SALES          CHICAGO      
   7902 FORD            20 RESEARCH       DALLAS       
   7934 MILLER          10 ACCOUNTING     NEW YORK     
   7521 WARD            30 SALES          CHICAGO      
14 rows selected.

Columns in the base EMP table of EMP_DEPT_OJ1 are modifiable through the view, because EMP is a key-preserved table in the join.

The following view also contains an outer join:

CREATE VIEW emp_dept_oj2 AS
    SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
    FROM emp e, dept d
    WHERE e.deptno (+) = d.deptno;

The statement

SELECT * FROM emp_dept_oj2;

results in:

EMPNO      ENAME      DEPTNO     DNAME          LOC
---------- ---------- ---------- -------------- ----
      7782 CLARK              10 ACCOUNTING     NEW YORK
      7839 KING               10 ACCOUNTING     NEW YORK
      7934 MILLER             10 ACCOUNTING     NEW YORK
      7369 SMITH              20 RESEARCH       DALLAS
      7876 ADAMS              20 RESEARCH       DALLAS
      7902 FORD               20 RESEARCH       DALLAS 
      7788 SCOTT              20 RESEARCH       DALLAS
      7566 JONES              20 RESEARCH       DALLAS
      7499 ALLEN              30 SALES          CHICAGO
      7698 BLAKE              30 SALES          CHICAGO
      7654 MARTIN             30 SALES          CHICAGO
      7900 JAMES              30 SALES          CHICAGO 
      7844 TURNER             30 SALES          CHICAGO
      7521 WARD               30 SALES          CHICAGO
                                 OPERATIONS     BOSTON
15 rows selected.

In this view, EMP is no longer a key-preserved table, because the EMPNO column in the result of the join can have nulls (the last row in the SELECT above). So, UPDATE, DELETE, and INSERT operations cannot be performed on this view.

In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is ``simple.'' For example:

SELECT col1, col2, ... FROM T;

that is, the select list of the view has no expressions, and there is no WHERE clause.

Consider the following set of views:

CREATE emp_v AS
  SELECT empno, ename, deptno
    FROM emp;

CREATE VIEW emp_dept_oj1 AS
  SELECT e.*, loc, d.dname
    FROM emp_v e, dept d
      WHERE e.deptno = d.deptno (+);

In these examples, EMP_V is merged into EMP_DEPT_OJ1 because EMP_V is a simple view, and so EMP is a key-preserved table. But if EMP_V is changed as follows:

CREATE emp_v_2 AS
  SELECT empno, ename, deptno
    FROM emp
      WHERE sal > 1000;

then, because of the presence of the WHERE clause, EMP_V_2 cannot be merged into EMP_DEPT_OJ1, and hence EMP is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, you can SELECT from the view USER_UPDATABLE_COLUMNS to see if it is. For example:

SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW';

might return:

OWNER		TABLE_NAME		COLUMN_NAM		UPD
----------	----------		----------		---
SCOTT		EMP_DEPT_V		EMPNO			NO
SCOTT		EMP_DEPT_V		ENAME			NO
SCOTT		EMP_DEPT_V		DEPTNO			NO
SCOTT		EMP_DEPT_V		DNAME			NO
SCOTT		EMP_DEPT_V		LOC				NO
5 rows selected.

View Name	Description
USER_UPDATABLE_COLUMNS	Shows all columns in all tables and views in the user's schema that are modifiable.
DBA_UPDATABLE_COLUMNS	Shows all columns in all tables and views in the DBA schema that are modifiable.
ALL_UPDATABLE_VIEWS	Shows all columns in all tables and views that are modifiable.