Insert into view which joins more than one table(INSERT,UPDATE or DELETE operation concept) and ORA-01776 (cannot modify more than one base table through a join view)

Updating a Join View

An updatable join view  is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.

The rules for updatable join views are shown in the following table. Views that meet these criteria are said to be inherently updatable.

Rule

General Rule

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

UPDATE Rule

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

DELETE Rule

Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. The key preserved table can be repeated in the FROM clause. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.

INSERT Rule

An INSERT statement must not explicitly or implicitly refer to the columns of a non-key-preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.

There are data dictionary views that indicate whether the columns in a join view are inherently updatable. 

DML Statements and Join Views

The general rule is that any UPDATE, DELETE, or INSERT statement on a join view can modify only one underlying base table. The following examples illustrate rules specific to UPDATE, DELETE, and INSERT statements.

Examples:

Examples illustrating the rules for inherently updatable join views, and a discussion of key-preserved tables, are presented in following sections. The examples in these sections work only if you explicitly define the primary and foreign keys in the tables, or define unique indexes.

 The following statements create 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),
      sal          NUMBER(7,2),
      comm         NUMBER(7,2),
      deptno       NUMBER(2),
      FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));


The following statement created the emp_dept join view which is referenced in the examples:

CREATE VIEW emp_dept AS

      SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc

      FROM emp, dept

      WHERE emp.deptno = dept.deptno

         AND dept.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

INSERT Statements

The following INSERT statement on the emp_dept view succeeds:

INSERT INTO emp_dept (ename, empno, deptno)

   VALUES ('KURODA', 9010, 40);



This statement works 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 the following, 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 (because there is no deptno 77).

INSERT INTO emp_dept (ename, empno, deptno)

   VALUES ('KURODA', 9010, 77);



The following INSERT statement would fail with an error (ORA-01776 cannot modify more than one base table through a join view):

INSERT INTO emp_dept (empno, ename, loc)

   VALUES (9010, 'KURODA', 'BOSTON');