File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Inserting Into a view that results from join of two tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Inserting Into a view that results from join of two tables" Watch "Inserting Into a view that results from join of two tables" New topic
Author

Inserting Into a view that results from join of two tables

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 281

I created a table tab1
create table tab1(stu_name varchar2(30),dept_id number);


I created another table tab2
create table tab2(dept_name varchar2(30),department_id number);


Then I created a view

Create view STView(sname,dname) as
select stu_name,dept_name
from tab1 join tab2
on dept_id=department_id;

Now When I try to insert into the view
insert into STview values('Alex','CS');
It syas

" cannot modify a column which maps to a non key-preserved table".

Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

It would be better if you posted a DDL statements you used to create the tables. Firstly, others would be able to test your example (see SSCCE), and secondly, in this case the existence (or the lack of) of primary keys and foreign constraints is crucial, and we don't see it from the descriptions.

Nevertheless, the error message mentions non key-preserved table. Thomas Kyte has probably the simplest definition what key-preserved view means:
Thomas Kyte wrote:Key preserved means the row from the base table will appear AT MOST ONCE in the output view on that table

See the entire discussion.

I haven't ever tried to insert into a join, but I'd say that it would only be possible if the tables in the join were in a 1:1 relationship (and declared by existing constraints as such), which is quite rare.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Inserting Into a view that results from join of two tables