Re: I am looking for references on updatable views
Date: 6 Oct 2005 12:37:33 -0700
Message-ID: <1128627453.587824.91800_at_g43g2000cwa.googlegroups.com>
>The entry of "updatable join views" Oracle in google seemed to produce
>several potentially useful hits including a tutorial on the use of
>updatable join views.
For newsgroup reference this is an edited portion of the oracle documentation on the subject.
An updatable join view (also referred to as a modifiable 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 as follows. Views that meet this criteria are said to be inherently updatable.
Rule Description
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. 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.
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.
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 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.
Arturo Hernandez Received on Thu Oct 06 2005 - 21:37:33 CEST