Re: ORA-01779, cannot get reason for

On 9 Apr 2006 09:13:29 -0700, "Andreas Piesk" <a.piesk_at_gmx.net> 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

<snip>

Oracle only allows update of a view when it can be established that every record of the table that is being updated appears at the most only once in the view result. In your case you are trying to update table m, but if a record of table m has x records of table c referencing it, this record of table m will appear x times in the view result.

Every record of c on the other hand only appears once in the view result, because every record of this table has at the most only one record of m associated with it through the foreign key.

Jaap. Received on Mon Apr 10 2006 - 15:08:21 CDT