ビューの難点：メインキーテーブルAbout Key-Parerrved Tables

UPDATE Emp_dept_view

  SET Sal = Sal * 1.10 

    WHERE Deptno = 10;

 
  
The following UPDATE statement would be disallowed on theEMP_DEPT_VIEW view:
 
   
  
UPDATE Emp_dept_view
  SET Loc = 'BOSTON'
WHERE Ename = 'SMITH';
 
  
This statement fails with an ORA-01779 error ("cannot modify a column which maps to a non key-preserved table"), because it attempts to modify the underlyingDEPT_TAB table, and theDEPT_TAB table is not key preserved in theEMP_DEPT view. 
 
   
  
In general, all modifiable columns of a join view must map to columns of a key-preserved table. If the view is defined using theWITHCHECKOPTION clause, then all join columns and all columns of repeated tables are not modifiable.
 
   
  
So, for example, if the EMP_DEPT view were defined usingWITH CHECKOPTION, then the followingUPDATE statement would fail:
 
   
  
UPDATE Emp_dept_view
    SET Deptno = 10
        WHERE Ename = 'SMITH';
 
  
The statement fails because it is trying to update a join column.
 
   
  
总结:更新一个复杂视图的条件是,只能更新主键表(Key-Preserved )的列;如果WITHCHECKOPTION ,则不能更新公共列; 
 
   
   
  
Deleting from a Join View 
 
   
   
   
  
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 theEMP_DEPT view: 
 
   
  
DELETE FROM Emp_dept_view
    WHERE Ename = 'SMITH';
 
  
This DELETE statement on theEMP_DEPT view is legal because it can be translated to aDELETE operation on the baseEMP_TAB table, and because theEMP_TAB table is the only key-preserved table in the join.
 
   
  
In the following view, a DELETE operation cannot be performed on the view because bothE1 and E2 are key-preserved tables:
 
   
  
CREATE VIEW emp_emp AS
    SELECT e1.Ename, e2.Empno, e1.Deptno
        FROM Emp_tab e1, Emp_tab e2
        WHERE e1.Empno = e2.Empno;
        WHERE e1.Empno = e2.Empno;
 
  
If a view is defined using the WITHCHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. For example:
 
   
  
CREATE VIEW Emp_mgr AS
    SELECT e1.Ename, e2.Ename Mname
       FROM Emp_tab e1, Emp_tab e2
            WHERE e1.mgr = e2.Empno
            WITH CHECK OPTION;
 
  
No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.
 
   
  
一句话总结：You can delete from a join view provided there isone and only one key-preserved table in the join. 
 
   
   
  
Inserting into a Join View 
 
   
   
   
  
The following INSERT statement on theEMP_DEPT view succeeds, because only one key-preserved base table is being modified (EMP_TAB), and 40 is a validDEPTNO in the  DEPT_TAB table (thus satisfying theFOREIGN KEY integrity constraint on theEMP_TAB table). 
 
   
  
INSERT INTO Emp_dept (Ename, Empno, Deptno)
    VALUES ('KURODA', 9010, 40);
 
  
The following INSERT statement fails for the same reason: ThisUPDATE on the base EMP_TAB table would fail: theFOREIGN KEY integrity constraint on theEMP_TAB table is violated. 
 
   
  
INSERT INTO Emp_dept (Ename, Empno, Deptno)
    VALUES ('KURODA', 9010, 77);
 
  
The following INSERT statement fails with anORA-01776 error ("cannot modify more than one base table through a view").
 
   
  
INSERT INTO Emp_dept (Ename, Empno, Deptno)
    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 theWITH CHECK OPTION clause, then you cannot perform anINSERT to it. 
 
   
  
总结：insert成功的条件only one key-preserved base table is being modified并且符合约束；
 
   
  
但如果在join视图中with check option那么你不能再insert了. 
 
   
   
  
相关视图 Three views you can use for modifying join views :USER_UPDATABLE_COLUMNS
 
   
   
  
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 (+);
Columns in the baseUPDATEtable ofEMP_DEPT_VIEWare modifiable through the view、becauseEMP_TABis a key-preserved table in the jun.
The follwing view also contains an outer jin:
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;
In this view、EMP_DEPT_OJ1isのlonger a key-preserved table、because theEMP_TABcolumn in the join can have nulls（the last row in theEMP_TABabove）・So、45914・45914。
Consder the following set of view:
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 intoEMP_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 theWHERE clause, EMP_V_2 cannot be merged into EMP_DEPT_OJ1, and henceEMP_TAB is no longer a key-preserved table. 
 
   
  
If you are in doubt whether a view is modifiable, then you canSELECT from the view USER_UPDATABLE_COLUMNS to see if it is
 
  
 
 
  
 
 
  
=================
 
  
http://www.geekinterview.com/talk/11040-key-preserved-table.html
 
  
A table is key preserved if every key of the table can also be a key of the result of the join. In key preserved table rows from the base appears at most once. Key preserved table guarantees to return only one copy of each row from the base table.

 
  
 
   

     Code: 
   
 
   
SQL> CREATE VIEW emp_dept AS 
  2  SELECT      a.empno, a.ename, a.sal , a.deptno, b.dname 
  3  FROM        emp a, dept b 
  4  WHERE       a.deptno = b.deptno ;

View created.

SQL> SELECT * FROM emp_dept;

    EMPNO ENAME            SAL    DEPTNO DNAME
--------- ---------- --------- --------- --------------
     7369 SMITH           5000        20 RESEARCH
     7499 ALLEN         2129.6        30 SALES
     7521 WARD         1663.75        30 SALES
     7566 JONES        3959.73        20 RESEARCH
     7654 MARTIN       1663.75        30 SALES
     7698 BLAKE        3793.35        30 SALES
     7782 CLARK        3260.95        10 ACCOUNTING
     7788 SCOTT           3993        20 RESEARCH
     7839 KING            6655        10 ACCOUNTING
     7844 TURNER        1996.5        30 SALES
     7876 ADAMS         1464.1        20 RESEARCH
     7900 JAMES        1264.45        30 SALES
     7902 FORD            3993        20 RESEARCH
     7934 MILLER        1730.3        10 ACCOUNTING

14 rows selected.
In the above example emp is key preserved table.Rows from EMP appars only once.DEPT is not a key preserved table.DEPTNO is key column in dept.But it is not a key column EMP_。DEPT view
in a join、a table is caled a key-preserve table iiiits keysarar preserved through the join.evrykey of the tablble can also be a key of therererereououououtititititititititime.evryprprininininkey or uniqukey or unqueeeeeeeeeeeeeebabababable thethethethethethethethethethethethethererererererererererererererererererererereree e e e e e e e thethethethethethethetheaaaaaaaaaaaaaaaaaaaaaaaajoin view not the table itself independently.a table may be key preseved in one join view and may not be key preserved in inother join view.B）it is not neceary for the kery columns of a selected in the the joviewit does not make the table key preserved.D)the key-preserved property of the table in the Table does not depend on the data inside the table.it depends on the schema design and the relanshibetweble.any dml operation can modify data from only one undering table.F)user can't refer to the columns of a non-key-preserved statment.G)delete operation can be performed on on a joview