alt
Advertisement
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow Oracle 10g arrow Oracle 10g Tutorials- Updating Views
Site Search


Oracle 10g Tutorials- Updating Views
Article Index
Oracle 10g Tutorials- Updating Views
Page 2
Page 3

.

.

.

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

The image cannot be displayed, because it contains errors.

Figure 17. Creating updateable join views

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.

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. For example, if in the emp table there was at most one employee in each department, then 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, 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

RESEARCHDALLAS


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.

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.

UPDATE Statements

The following example shows an UPDATE statement that successfully modifies the emp_dept view:

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

The image cannot be displayed, because it contains errors.

Figure 18. Updating join views

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

In general, all updatable 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 taken from tables that are referenced more than once in the view 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.

If you were to create the following view, a DELETE operation could not 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.

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

The image cannot be displayed, because it contains errors.

Figure 19. Deleting from views

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



 
< Prev   Next >
Exforsys Offers
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape