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 view):

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

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.

Updating Views that Involve Outer Joins

Views that involve outer joins are modifiable in some cases. For example:

CREATE VIEW Emp_dept_oj1 AS
SELECT Empno, Ename, e.Deptno, Dname, Loc
FROM Emp_tab e, Dept_tab d
WHERE e.Deptno = d.Deptno (+);

The statement:

SELECT * FROM Emp_dept_oj1;

Results in:

14 rows selected.

Columns in the base emp_tab table of emp_dept_oj1 are modifiable through the view, because emp_tab 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_tab e, Dept_tab d
WHERE e.Deptno (+) = d.Deptno;

The statement:

SELECT * FROM Emp_dept_oj2;

Results in:

In this view, emp_tab 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 preceding SELECT statement). 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;

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

Consider the following set of views:

CREATE VIEW Emp_v AS
SELECT Empno, Ename, Deptno
FROM Emp_tab;

CREATE VIEW Emp_dept_oj1 AS
SELECT e.*, Loc, d.Dname
FROM Emp_v e, Dept_tab 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_tab is a key-preserved table. But if emp_v is changed as follows:

CREATE VIEW Emp_v_2 AS
SELECT Empno, Ename, Deptno
FROM Emp_tab
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_tab is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, then 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';

This might return:

5 rows selected.

First Page: Oracle 10g Tutorials- Updating Views