updatable view question

Hello,

I have read the document about the updatable view in oracle doc. "oracle application developer guide" chapeter4. but I'm not quite understand the meaning of "key-preseved table".

The doc give an example as follow:

CREATE TABLE dept (
deptno NUMBER(4) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13));

CREATE TABLE emp (
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(DEPTNO)); CREATE or replace VIEW emp_dept AS
SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e, dept d /* JOIN operation */
WHERE e.deptno = d.deptno
AND d.loc IN ('DALLAS','NEW YORK','BOSTON');

SQLWKS> select * from user_updatable_columns where table_name=upper('emp_dept')

     2>
OWNER                          TABLE_NAME
COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------


------------------------------ --- --- ---

SCOTT                          EMP_DEPT                       EMPNO
                     YES YES YES
SCOTT                          EMP_DEPT                       ENAME
                     YES YES YES
SCOTT                          EMP_DEPT                       DEPTNO
                     YES YES YES
SCOTT                          EMP_DEPT                       DNAME
                     NO  NO  NO
SCOTT                          EMP_DEPT                       LOC
                     NO  NO  NO

I quota the two paragraph in the doc. below:

"In this view(EMP_DEPT), 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."

"The following UPDATE statement would be disallowed on the EMP_DEPT view:

UPDATE emp_dept
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 underlying
DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view."

I tried to modified the view as follow:

CREATE or replace VIEW emp_dept AS
SELECT e.empno, e.ename, d.deptno, d.dname, d.loc FROM emp e, dept d /* JOIN operation */
WHERE e.deptno = d.deptno
AND d.loc IN ('DALLAS','NEW YORK','BOSTON');

SQLWKS> select * from user_updatable_columns where table_name=upper('emp_dept')

     2>
OWNER                          TABLE_NAME
COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------


------------------------------ --- --- ---

SCOTT                          EMP_DEPT                       EMPNO
                     YES YES YES
SCOTT                          EMP_DEPT                       ENAME
                     YES YES YES
SCOTT                          EMP_DEPT                       DEPTNO
                     NO  NO  NO
SCOTT                          EMP_DEPT                       DNAME
                     NO  NO  NO
SCOTT                          EMP_DEPT                       LOC
                     NO  NO  NO

• I don't know why DEPTNO , DNAME, LOC in the view are still not updatable as I include the key of dept table (deptno) in the create view statement?

     All the columns of dept table had been included in the create view statement,

     but it seems that DEPT table is still not a key -preserved in the EMP_DEPT view.

     Can someone  give me some hints on this.
     Am I misunderstand something?
-----------------------------------------------------------------------------------------------------------------------

Question (2)
I have created three table and two views to clarify the concept of updatable view.
testa and testb , each has only one key(cola) while testc had a composite key(cola,cold).
If I create a view(vw_testab) on testa and testb, all the columns are updatable.
but if create a view(vw_testac) on testa and testc, all the columns select from testc
is updatable but all the columns select from testa is not updatable. Why?

Is it possible to update testa columns in the view (vw_testac)? Any alternative method?

SQLWKS> desc testa

Column Name                    Null?    Type
------------------------------ -------- ----
COLA                           NOT NULL NUMBER
COLB                                    VARCHAR2(5)
COLC                                    NUMBER
SQLWKS> desc testb
Column Name                    Null?    Type
------------------------------ -------- ----
COLA                           NOT NULL NUMBER
COLB                                    VARCHAR2(5)
COLC                                    NUMBER
SQLWKS> desc testc
Column Name                    Null?    Type
------------------------------ -------- ----
COLA                           NOT NULL NUMBER
COLB                                    VARCHAR2(5)
COLC                                    NUMBER
COLD                           NOT NULL DATE

create view vw_testab as
select a.cola,b.cola,a.colb,b.colb,a.colc,b.colc from testa a,testb b
where a.cola = b.cola

create view vw_testac as
select a.cola,c.cola,a.colb,c.colb,a.colc,c.colc,c.cold from testa a, testc c
where a.cola=c.cola

SQLWKS> select * from user_updatable_columns where table_name=upper('vw_testab')

     2>
OWNER                          TABLE_NAME
COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------


------------------------------ --- --- ---

SCOTT                          VW_TESTAB                      A_COLA
                     YES YES YES
SCOTT                          VW_TESTAB                      B_COLA
                     YES YES YES
SCOTT                          VW_TESTAB                      A_COLB
                     YES YES YES
SCOTT                          VW_TESTAB                      B_COLB
                     YES YES YES
SCOTT                          VW_TESTAB                      A_COLC
                     YES YES YES
SCOTT                          VW_TESTAB                      B_COLC
                     YES YES YES

SQLWKS> select * from user_updatable_columns where table_name=upper('vw_testac')

     2>
OWNER                          TABLE_NAME
COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------


------------------------------ --- --- ---

SCOTT                          VW_TESTAC                      A_COLA
                     NO  NO  NO
SCOTT                          VW_TESTAC                      C_COLA
                     YES YES YES
SCOTT                          VW_TESTAC                      A_COLB
                     NO  NO  NO
SCOTT                          VW_TESTAC                      C_COLB
                     YES YES YES
SCOTT                          VW_TESTAC                      A_COLC
                     NO  NO  NO
SCOTT                          VW_TESTAC                      C_COLC
                     YES YES YES
SCOTT                          VW_TESTAC                      C_COLD
                     YES YES YES

Sorry for asking so many question at the same time, I have tried to search internet on the concept of updatable view but didn't figure out a clear picture on this.

Thanks

W. Received on Sun Oct 28 2001 - 04:43:33 CST