ORA-01779, cannot get reason for

Dear all,

i'm trying to understand the concept of key-preserved tables but failed so far. let me give you a trimmed down version of my objects:

> create table m (id number primary key, col number);
> insert into m values(1,1);
> create table c (id number primary key, fk_m number references m, col number);
> insert into c values(1,1,1);

create or replace view v as select m.id, m.col m_col, c.col c_col from m left join c on m.id = c.fk_m;
> select * from v;

        ID M_COL C_COL
---------- ---------- ----------

         1 1 1

so far, so good. now an update:
> update v set m_col = 2 where id=1;

update v set m_col = 2 where id=1

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

why? m_col == m.col is directly dependant from PK id == m.id. i simply don't get it.
i understood that c_col isn't updatable, because of the left join. if i change the view to
> create or replace view v as select m.id, m.col m_col, c.col c_col from m join c on m.id = c.fk_m;

i can update c_col but still not m_col which is illogical (at least to me):

> update v set c_col = 2 where id=1;

1 row updated.
> update v set m_col = 2 where id=1;

update v set m_col = 2 where id=1

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

can anyone enlighten me, please? why cannot m_col be updated?

i'm using 9.2.0.6 and 10.2.0.2.

regards,
-ap Received on Sun Apr 09 2006 - 11:13:29 CDT