ORA-01779: 无法修改与非键值保存表对应的列

当我更新一个视图的时候，遇到这个错误：ORA-01779: 无法修改与非键值保存表对应的列

01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
//         map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.

可以来模拟这个错误：

SQL> desc gw2
Name    Type    Nullable Default Comments
------- ------- -------- ------- --------
SID     INTEGER Y                        
OLD_SID INTEGER Y                        

SQL> select * from gw1;
 
                                     ID                                     SID
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      2                                       2
                                      3                                       3
                                      4                                       4
 
SQL> select * from gw2;
 
                                    SID                                 OLD_SID
--------------------------------------- ---------------------------------------
                                      1                                      11
                                      2                                      22
                                      3                                      33
 
SQL> update (select gw1.sid,gw2.old_sid from gw1,gw2 where gw1.sid=gw2.sid) s set s.sid=s.old_sid;
 
update (select gw1.sid,gw2.old_sid from gw1,gw2 where gw1.sid=gw2.sid) s set s.sid=s.old_sid
 
ORA-01779: 无法修改与非键值保存表对应的列

这是因为，不能保证gw1中的一个sid对应一个唯一的gw2表的sid，也就是说gw2的sid不能保证唯一，解决的方法就是将gw2的sid加上unique约束。

SQL> alter table gw2 modify sid unique;
 
Table altered
 
SQL> update (select gw1.sid,gw2.old_sid from gw1,gw2 where gw1.sid=gw2.sid) s set s.sid=s.old_sid;
 
3 rows updated

这样就可以了。

如需转载，请注明出处：http://blog.csdn.net/nanaranran/article/details/17508557