Re: ORA-01779, cannot get reason for

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 09 Apr 2006 13:56:42 -0700
Message-ID: <1144616203.153124@yasure.drizzle.com>

Andreas Piesk wrote:
> 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

Your view only has a single primary key, m.id, but not: c.id.

A solution can be found in Morgan's Library at www.psoug.org under INSTEAD-OF triggers.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sun Apr 09 2006 - 15:56:42 CDT