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

advertisements

_____________________________________________________________________________________________________________________

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