视图中的难点:主键表 About Key-Preserved Tables

http://wmlm.itpub.net/post/12871/278640

因为在项目中大量地使用了视图，而在视图上的更新上产生了一点儿问题，所以抽时间对可更新视图进行了复习，英文看得多了，也就成了中文

 

测试用表

CREATE TABLE Dept_tab (
Deptno NUMBER(4) PRIMARY KEY,
Dname VARCHAR2(14),
Loc VARCHAR2(13));

CREATE TABLE Emp_tab (
Empno NUMBER(4) PRIMARY KEY,
Ename VARCHAR2(10),
Job varchar2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2),
FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno));

CREATE VIEW Emp_dept_view AS
SELECT e.Empno, e.Ename, e.Deptno, e.Sal, d.Dname, d.Loc
FROM Emp_tab e, Dept_tab d /* JOIN operation */
WHERE e.Deptno = d.Deptno
AND d.Loc IN ('DALLAS', 'NEW YORK', 'BOSTON');In this view,EMP_TAB is a key-preserved table, becauseEMPNO is a key of theEMP_TAB table, and also a key of the result of the join.DEPT_TAB isnot a key-preserved table, because although DEPTNO is a key of theDEPT_TAB table, it is not a key of the join.

Rule for DML Statements on Join Views

Any UPDATE,INSERT, orDELETE statement on a join view can modifyonly one underlying base table.

Updating a Join View

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

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 usingWITHCHECKOPTION, 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 andE2 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 WITHCHECKOPTION 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 theFOREIGNKEY 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 baseEMP_TAB table would fail: theFOREIGNKEY 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 theWITHCHECK 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 base EMP_TAB table ofEMP_DEPT_OJ1 are modifiable through the view, becauseEMP_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;

In this view, EMP_TAB is no longer a key-preserved table, because theEMPNO column in the result of the join can have nulls (the last row in theSELECT above). So,UPDATE, DELETE, and INSERT operations cannot be performed on this view.

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 intoEMP_DEPT_OJ1 becauseEMP_V is a simple view, and so EMP_TAB is a key-preserved table. But ifEMP_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 viewUSER_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 ACCOUNTING14 rows selected.

In the above example emp is key preserved table. Rows from EMP appears only once. DEPT is not a key preserved table. DEPTNO is key column in dept. But it is not a key column in EMP_DEPT view.

in a join,a table is called a key-preserve table if its keys are preserved through the join.every key of the table can also be a key of the resultant join resultset.every primary key or unique key value in the base table must also be unique in the result set of the join.
A)key-preservation is a property of the table inside the join view not the table itself independently.a table may be key preseved in one join view and may not be key preserved in another join view.
B)it is not necessary for the key columns of a table to be selected in the join view for the table to be key preserved.
C)if the key columns of a table is selected in the view defination ,it does not make the the table key preserved.
D)the key-preserved property of the table in a join view does not depend on the data inside the table.it depends on the schema design and the relationship between the tables.
E)a join view may select data from many tables ,any dml operation can modify data from only one underlying table.
F)user can't refer to the columns of a non-key-preserved table in an insert statment.
G)delete operation can be performed on a join view if the join view has one and only one key-preserved table.