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 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