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');
