Home » SQL & PL/SQL » SQL & PL/SQL » views could not be updated :no-key reserved table (oracle 9i)
views could not be updated :no-key reserved table [message #325887] Mon, 09 June 2008 07:10 Go to next message
souvik_roy
Messages: 16
Registered: June 2008
Junior Member
Hi,

I 've created a table employee which have columns (emp_id primary key,last_name,dep_id foreign key to dep_id col of department,mng_id).

The department table contains dep_id primary key and dep_name,

I've created a view through the statement:
create or replace view emp_vw
as
select e.emp_id ,e.last_name ,d.dep_id
from employee e,department d
where d.dep_id(+)=e.dep_id
with check option;

But when i'm trying to run the following statement:
update emp_vw set dep_id=55 where emp_id=104
it's showing the error :"ORA-01779: cannot modify a column which maps to a non key-preserved table"
Could u please give me the reason for that....

Thanks,
Souvik
Re: views could not be updated :no-key reserved table [message #325889 is a reply to message #325887] Mon, 09 June 2008 07:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle
ORA-01779: 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.

Re: views could not be updated :no-key reserved table [message #325892 is a reply to message #325889] Mon, 09 June 2008 07:26 Go to previous messageGo to next message
souvik_roy
Messages: 16
Registered: June 2008
Junior Member
Hi,

I've done it as you told me to update the view,but I was trying to know why the column is not getting updated like the other columns of that view.
Re: views could not be updated :no-key reserved table [message #325895 is a reply to message #325892] Mon, 09 June 2008 07:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Because you are trying to update a column which is used in the view's JOIN clause.

Table's primary key (if it was part of the view) could "serve" as view's primary key. In that case, table would be "key preserved" in a view. In your case, there can be more than one record in the "employees" table pointing to the same record in the "departments" table so, this column can not be view's primary key.
Re: views could not be updated :no-key reserved table [message #325899 is a reply to message #325887] Mon, 09 June 2008 07:56 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Key-preserved table concept in join view

Regards
Michel
Previous Topic: Retrieving a primary key
Next Topic: Disable validate
Goto Forum:
  


Current Time: Sat Dec 10 03:00:28 CST 2016

Total time taken to generate the page: 0.10375 seconds