| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: updatable view question
"Cantonese Boy" <waynewan_at_yahoo.com> wrote in message
news:3BDBE155.33BAC706_at_yahoo.com...
> 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!¡L), 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?
> --------------------------------------------------------------------------
It is in the docs though.
The result of the view is *uniquely identified* by the empno column.
The result of the view is *not* uniquely identfied by the deptno column.
So you can update whatever is dependent from the empno column, but not
anything that is derived from the deptno column.
Of course there is -as of 8i- a solution to this 'problem' by using instead
of triggers.
Personally I don't think the update you showed on emp_dept where a location
is updated by selecting an employee is good programming practice.
Hth,
Sybrand Bakker
Senior Oracle DBA
Received on Sun Oct 28 2001 - 06:13:31 CST
 |
 |