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

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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.

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer