Insert to Oracle View failed with ORA-01779: cannot modify a column which maps to a non key-preserved table

Error description:
ORA-01779 error is coming while inserting into view

Solution Description:
Views are two types in terms of update to the view-Updateable and non-updateable. When you try to insert to the non-updateable views you will get the error ORA-01779: cannot modify a column which maps to a non key-preserved table


Example
==== Creating non-updateable view and here you get the error message.
SQL> create view emp_dept as select ename, dname from emp, dept where emp.deptno=dept.deptno;
View created.
SQL> insert into emp_dept (select * from emp_dept);
insert into emp_dept (select * from emp_dept)
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

==== Creating Updateable view and here you do not get the error message while insertion.
SQL> drop view emp_dept;
View dropped.
SQL> create view v_emp as select ename from emp ;
View created.
SQL>  insert into v_emp (select * from v_emp);
14 rows created.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google