You Asked

I see a lot of examples where primary keys are missing or indexes are not unique. But I didn't see anything matching this example. There are 3 tables joined, and the 3rd table has a compound key that gets one value from the first table and one from the second table.

Given this:
create table A (x number, data varchar(10));
create table B (p number, q number);
create table C (r number, s number, data varchar(10));
alter table B add constraint B_PK primary key (p);
alter table C add constraint C_PK primary key (r,s);

And these two update statements, where the only difference is in the join with the third table:

update
(select A.x
, A.data
, C.data as new_data
from A
join B on A.x = B.p
join C on A.x = C.r and B.q = C.s
)
set data = new_data;

update
(select A.x
, A.data
, C.data as new_data
from A
join B on A.x = B.p
join C on B.p = C.r and B.q = C.s
)
set data = new_data;

Why does the first update fail with [ORA-01779: cannot modify a column which maps to a non key-preserved table], while the second one succeeds? They are logically equivalent as far as I can tell.

and Connor said...

I agree, they are logically equivalent, but thats "just the way it is".

When we are examining the view to see if its key-preserved, we're going to take a cautious approach, because whilst its safe to say "no its not key-preserved" when in fact it might be, the opposite is not true. If we ever made a mistake about our key-preserved decision...that would be a data corruption disaster waiting to happen.

You can always see our decisions by using views and checking the appropriate dictionary view, eg

SQL> create or replace view v1 as
  2  select A.x
  3  , A.data
  4  , C.data as new_data
  5  from A, B, C
  6  where A.x = B.p
  7  and   A.x = C.r
  8  and   B.q = C.s
  9  /

View created.

SQL> create or replace view v2 as
  2  select A.x
  3  , A.data
  4  , C.data as new_data
  5  from  A, B, C
  6  where A.x = B.p
  7  and   B.p = C.r
  8  and   B.q = C.s
  9  /

View created.

SQL> select * from user_updatable_columns
  2  where table_name in ('V1','V2');

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
MCDONAC                        V1                             X                              NO  NO  NO
MCDONAC                        V1                             DATA                           NO  NO  NO
MCDONAC                        V1                             NEW_DATA                       NO  NO  NO
MCDONAC                        V2                             X                              YES YES YES
MCDONAC                        V2                             DATA                           YES YES YES
MCDONAC                        V2                             NEW_DATA                       NO  NO  NO

6 rows selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 30, 2016 - 1:26 pm UTC

March 31, 2016 - 3:06 am UTC

Not really, but we're doing some AskTom changes soon to increase the metadata associated with each question, so in future we can show "related questions" and such

A reader, March 30, 2016 - 1:32 pm UTC

Will this be added to
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:548422757486
?

updatable join views in 12.2

Rajeshwaran Jeyabal, October 16, 2018 - 2:08 pm UTC

Team,

was reading through this concepts guide on "updatable join" views.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/partitions-views-and-other-schema-objects.html#GUID-8C983FA8-0E9B-4D2B-B819-28FD7F45F1FA

demo@ORA12C> create table emp as select * from scott.emp;

Table created.

demo@ORA12C> create table dept as select * from scott.dept;

Table created.

demo@ORA12C> create or replace view emp_vw
  2  as
  3  select empno,ename,dname
  4  from emp e, dept d
  5  where e.deptno = d.deptno ;

View created.

demo@ORA12C> select *
  2  from user_updatable_columns
  3  where table_name ='EMP_VW' ;

OWNER      TABLE_NAME           COLUMN_NAME          UPD INS DEL
---------- -------------------- -------------------- --- --- ---
DEMO       EMP_VW               EMPNO                NO  NO  NO
DEMO       EMP_VW               ENAME                NO  NO  NO
DEMO       EMP_VW               DNAME                NO  NO  NO

demo@ORA12C> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

demo@ORA12C> alter table emp add constraint emp_fk foreign key(deptno) references dept;

Table altered.

demo@ORA12C> select *
  2  from user_updatable_columns
  3  where table_name ='EMP_VW' ;

OWNER      TABLE_NAME           COLUMN_NAME          UPD INS DEL
---------- -------------------- -------------------- --- --- ---
DEMO       EMP_VW               EMPNO                NO  NO  NO
DEMO       EMP_VW               ENAME                NO  NO  NO
DEMO       EMP_VW               DNAME                NO  NO  NO

demo@ORA12C> alter table emp modify deptno not null ;

Table altered.

demo@ORA12C> select *
  2  from user_updatable_columns
  3  where table_name ='EMP_VW' ;

OWNER      TABLE_NAME           COLUMN_NAME          UPD INS DEL
---------- -------------------- -------------------- --- --- ---
DEMO       EMP_VW               EMPNO                NO  NO  NO
DEMO       EMP_VW               ENAME                NO  NO  NO
DEMO       EMP_VW               DNAME                NO  NO  NO

the user_updatable_columns shows all the columns in the view are not available for insert/update/delete.

but delete and update on this view for EMP table columns got succeeded. (since it is a key preserved table), though the dictionary show it is not possible to delete and insert - please advice.

demo@ORA12C> update emp_vw set ename ='x' where mod(empno,2) = 0 ;

10 rows updated.

demo@ORA12C> delete from emp_vw ;

14 rows deleted.

demo@ORA12C> select count(*) from emp_vw;

  COUNT(*)
----------
         0

demo@ORA12C> select count(*) from emp ;

  COUNT(*)
----------
         0

demo@ORA12C> select count(*) from dept ;

  COUNT(*)
----------
         4

demo@ORA12C> rollback ;

Rollback complete.

demo@ORA12C> update emp_vw set dname ='XXX' where mod(empno,2) = 0;
update emp_vw set dname ='XXX' where mod(empno,2) = 0
                  *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


demo@ORA12C>

October 20, 2018 - 4:51 pm UTC

The view needs to be compiled to reflect the changes the underlying data dictionary

SQL> create table emp as select * from scott.emp;

Table created.

SQL> create table dept as select * from scott.dept;

Table created.

SQL> create or replace view emp_vw
  2      as
  3      select e.empno,e.ename,d.dname
  4      from emp e, dept d
  5      where e.deptno = d.deptno ;

View created.

SQL>
SQL> select *
  2      from user_updatable_columns
  3      where table_name ='EMP_VW' ;

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
MCDONAC                        EMP_VW                         EMPNO                          NO  NO  NO
MCDONAC                        EMP_VW                         ENAME                          NO  NO  NO
MCDONAC                        EMP_VW                         DNAME                          NO  NO  NO

3 rows selected.

SQL>
SQL> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

SQL>
SQL> alter table emp add constraint emp_pk primary key(empno);

Table altered.

SQL>
SQL> alter table emp add constraint emp_fk foreign key(deptno) references dept;

Table altered.

SQL> select *
  2      from user_updatable_columns
  3      where table_name ='EMP_VW' ;

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
MCDONAC                        EMP_VW                         EMPNO                          NO  NO  NO
MCDONAC                        EMP_VW                         ENAME                          NO  NO  NO
MCDONAC                        EMP_VW                         DNAME                          NO  NO  NO

3 rows selected.

SQL> alter table emp modify deptno not null ;

Table altered.

SQL> select *
  2      from user_updatable_columns
  3      where table_name ='EMP_VW' ;

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
MCDONAC                        EMP_VW                         EMPNO                          NO  NO  NO
MCDONAC                        EMP_VW                         ENAME                          NO  NO  NO
MCDONAC                        EMP_VW                         DNAME                          NO  NO  NO

3 rows selected.

SQL>
SQL> alter view EMP_VW compile;

View altered.

SQL>
SQL> select *
  2      from user_updatable_columns
  3      where table_name ='EMP_VW' ;

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
MCDONAC                        EMP_VW                         EMPNO                          YES YES YES
MCDONAC                        EMP_VW                         ENAME                          YES YES YES
MCDONAC                        EMP_VW                         DNAME                          NO  NO  NO

3 rows selected.

SQL>

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

updatable join views in 12.2

More to Explore

PL/SQL demos

PL/SQL docs

Key-preserved table concept in join view

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

updatable join views in 12.2

More to Explore

PL/SQL demos

PL/SQL docs