当我更新一个视图的时候,遇到这个错误: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