Cannot modify a column which maps to a non key-preserved table error while trying to insert into a view

Question

I have t1 table having no number(3) and name varchar2(20) columns

And emp table having empno,ename,job etc columns.

Now I am creatng a view

create view v_t as select * from t1,emp;

It creates view. Then I am trying to insert values inside v_t that is view but it gives me error that

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

here is one link

What am I doing wrong?

Answer 1

Assuming that both the t1 table and emp table have primary keys that the view can identify as unique, I'm going to guess that the issue is with how you are specifying your JOIN. What you've got right now looks like it will create a Cartesian product (every row from one table indiscriminately joined to every row from the other), and that's probably not going to satisfy the key-preserved table requirement (referenced in the question that you linked above).

You are specifying an implicit JOIN in your FROM clause, but I don't see any JOIN conditions (read: WHERE clause). By looking at your schema, I'm going to assume that you can JOIN on t1.no = emp.empno like this:

create view v_t as 
select * 
from t1,emp
where t1.no = emp.empno;

Or with an explicit JOIN:

create view v_t as
select *
from t1
inner join emp on emp.empno = t1.no;

Oracle will allow a NATURAL JOIN (without specifying JOIN conditions) on tables that have columns of the same type and name. Of course, since no and empno have different names, that's not going to work.

Now that your CREATE VIEW is squared-away, let's get to the INSERT. You can INSERT into a VIEW based on a JOIN as long as the table is key-preserved and you don't try to INSERT to more than one base table at once. Which in your case means writing two separate INSERT statements or writing an INSTEAD OF trigger:

CREATE TRIGGER v_t_insteadof
INSTEAD OF INSERT ON v_t

FOR EACH ROW
BEGIN 

INSERT INTO t1 (no, name)
VALUES(:new.no, :new.name); 

INSERT INTO emp (empno, ename, job)
VALUES(:new.no, :new.ename, :new.job); 

END v_t_insteadof;

Note, that you'll need to adjust the INSERT INTO emp based-on the additional fields that might exist in that table. Also, your INSERT command will need to specifically name the fields:

INSERT INTO v_t (no, name, ename, job) VALUES (59, 'Bob', 'Bobs Ename', 'Bobs Job');