Inserting Into a view that results from join of two tables

[Mahtab Alam]

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".

[Martin Vashko]

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.