ORA 01779 : cannot modify a column that maps to a non key preserved table

[oracle_guy]

I have 2 db tables

emp(empID, empname, deptID)
dept(deptID, deptName)

I have the following view that is created successfully

Create view E_VIEW as
Select empName, deptName
From emp, dept
Where emp.deptID = dept.deptID;



I have the following store procedure created successfully that calls and inserts into the above view, the empname and deptName.

CREATE or REPLACE procedure INSERT_TO_VIEW(
p_ename in varchar2,
p_dname in varchar)
IS
BEGIN
INSERT into E_VIEW values(p_ename, p_dname);
commit;
end;
/

However my calling routine with the following code generates the below mentioned error

Begin
INSERT_TO_VIEW('Mark', 'Sales');
end;
/

ORA-1779 error cannot modify a column which maps to a non key

One thing I want to mention is that the empID in the emp table is a non null field and is generated by a sequence next val (EmpSeq.nextval).

Do I need to take care of this or is the error due to something else??

[oratune]

it doesn't matter that empID is a not null field, it isn't unique therefore it cannot be used to identify each record in the underlying base table. Also you cannot modify more than one base table to a view, even if you set up the primary keys:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3  end;
  4  /
Begin
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
ORA-06512: at "BING.INSERT_TO_VIEW", line 6
ORA-06512: at line 2


SQL>

You'll need an INSTEAD OF trigger to perform the updates you desire.  Starting over from the beginning:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create sequence newdeptseq
  2  start with 10 increment by 10 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> create or replace trigger newdeptid_trg
  2  before insert on newdept
  3  for each row
  4  begin
  5          select newdeptseq.nextval
  6          into :new.deptid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger e_view_insert
  2  instead of insert on e_view
  3  for each row
  4  declare
  5          dept_id number;
  6  begin
  7          insert into newdept
  8          (deptname)
  9          values(:new.deptname);
 10
 11          select deptid
 12          into dept_id
 13          from newdept
 14          where deptname = :new.deptname;
 15
 16          insert into newemp
 17          (empname, deptid)
 18          values(:new.empname, dept_id);
 19
 20  end;
 21  /

Trigger created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from e_view;

EMPNAME                                  DEPTNAME
---------------------------------------- ------------------------------
Mark                                     Sales

SQL>
SQL> select *
  2  from newemp;

     EMPID EMPNAME                                      DEPTID
---------- ---------------------------------------- ----------
         1 Mark                                             10

SQL>
SQL> select *
  2  from newdept;

    DEPTID DEPTNAME
---------- ------------------------------
        10 Sales

SQL>


David Fitzjarrell

[oracle_guy]

David,
Thanks very much. I now have a clear idea. 
One thing I wanted to clarify is that  do we STILL have to have a trigger for  dept table in case I want to conduct NEW INSERT operations on EMP in  an existing department? All I want to do is to be able to add new employees to an existing dept.

Thanks once again

[oracle_guy]

I am  getting an error "ORA-01733: virtual column not allowed here " at the 14th line of the e-view-insert trigger. I am using the entire code. Everything else executes successfully. Only at this I see those errors.

[oracle_guy]

I finally got everything to execute successfully. Sorry about my 2 earlier posts where I posted regarding some errors. Theye were due to some spell check issues. I apologize.

I have a different issue though. The current code allows me to add a new employee and a new dept for that employee successfully. I want to be able to add a new employee to an existing dept. When I try doing that I get an error
ORA-01422: exact fetch returns more than requested number of rows
ORA -04088 : error during execution of trigger e_view_insert
ORA -06512: at lines 6 and 9 which insert the new dept name in the new dept table.
Thanks David

[oratune]

David,
Thanks very much. I now have a clear idea. 
One thing I wanted to clarify is that  do we STILL have to have a trigger for  dept table in case I want to conduct NEW INSERT operations on EMP in  an existing department? All I want to do is to be able to add new employees to an existing dept.

Thanks once again

No, that trigger isn't necessary if you're expecting to add new employees to an existing department, although you do need to supply the deptid when adding new departments:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger e_view_insert
  2  instead of insert on e_view
  3  for each row
  4  declare
  5          dept_id number;
  6  begin
  7          select deptid
  8          into dept_id
  9          from newdept
 10          where deptname = :new.deptname;
 11
 12          insert into newemp
 13          (empname, deptid)
 14          values(:new.empname, dept_id);
 15
 16  exception
 17          when no_data_found then
 18                  raise_application_error(20001, 'Department does not exist', true);
 19  end;
 20  /

Trigger created.

SQL>
SQL> CREATE or REPLACE procedure INPUT_RECORDS(
  2          p_ename in varchar2,
  3          p_dname in varchar2)
  4  IS
  5  BEGIN
  6          INSERT into newemp(empid, empname, deptID)
  7           select newEmpSeq.nextval, p_ename, deptID
  8           FROM
  9           newdept where deptname = p_dname;
 10          commit;
 11  end;
 12  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> insert into newdept
  2  values (10, 'Sales');

1 row created.

SQL>
SQL> insert into newdept
  2  values (20, 'Marketing');

1 row created.

SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> Begin
  2          INPUT_RECORDS('Clyde', 'Marketing');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from e_view;

EMPNAME                                  DEPTNAME
---------------------------------------- ------------------------------
Mark                                     Sales
Clyde                                    Marketing

SQL>
SQL> select *
  2  from newemp;

     EMPID EMPNAME                                      DEPTID
---------- ---------------------------------------- ----------
         1 Mark                                             10
         3 Clyde                                            20

SQL>
SQL> select *
  2  from newdept;

    DEPTID DEPTNAME
---------- ------------------------------
        10 Sales
        20 Marketing

SQL>


David Fitzjarrell

[oracle_guy]

Thanks again David,
Since I am a newbie, looks like my questions evolve with answers. Last one I have is
1. how do we manage to add a new employee to either an existing or a totally new department at the same time.  All we need to do is to supply the emp_name and dept_name. The values should get inserted by either creating a new deptName(and deptID)or by using an existing dept name. I assume we need the earlier newdept trigger, but the combination may conflict if deptname already exists.
Thanks

[oratune]

Thanks again David,
Since I am a newbie, looks like my questions evolve with answers. Last one I have is
1. how do we manage to add a new employee to either an existing or a totally new department at the same time.  All we need to do is to supply the emp_name and dept_name. The values should get inserted by either creating a new deptName(and deptID)or by using an existing dept name. I assume we need the earlier newdept trigger, but the combination may conflict if deptname already exists.
Thanks

It won't if you understand how PL/SQL functions:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create sequence newdeptseq
  2  start with 10 increment by 10 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> create or replace trigger newdeptid_trg
  2  before insert on newdept
  3  for each row
  4  begin
  5          select newdeptseq.nextval
  6          into :new.deptid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger e_view_insert
  2  instead of insert on e_view
  3  for each row
  4  declare
  5          dept_id number;
  6  begin
  7          select deptid
  8          into dept_id
  9          from newdept
 10          where deptname = :new.deptname;
 11
 12          insert into newemp
 13          (empname, deptid)
 14          values(:new.empname, dept_id);
 15
 16  exception
 17          when no_data_found then
 18                  insert into newdept
 19                  (deptname)
 20                  values(:new.deptname)
 21                  returning deptid into dept_id;
 22
 23                  insert into newemp
 24                  (empname, deptid)
 25                  values(:new.empname, dept_id);
 26
 27  end;
 28  /

Trigger created.

SQL>
SQL> CREATE or REPLACE procedure INPUT_RECORDS(
  2          p_ename in varchar2,
  3          p_dname in varchar2)
  4  IS
  5  BEGIN
  6          INSERT into newemp(empid, empname, deptID)
  7           select newEmpSeq.nextval, p_ename, deptID
  8           FROM
  9           newdept where deptname = p_dname;
 10          commit;
 11  end;
 12  /

Procedure created.

SQL>
SQL> insert into newdept
  2  (deptname)
  3  values('Marketing');

1 row created.

SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3          INSERT_TO_VIEW('Zappo', 'Finance');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> Begin
  2          INPUT_RECORDS('Clyde', 'Marketing');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from e_view;

EMPNAME                                  DEPTNAME
---------------------------------------- ------------------------------
Mark                                     Sales
Zappo                                    Finance
Clyde                                    Marketing

SQL>
SQL> select *
  2  from newemp;

     EMPID EMPNAME                                      DEPTID
---------- ---------------------------------------- ----------
         1 Mark                                             20
         2 Zappo                                            30
         4 Clyde                                            10

SQL>
SQL> select *
  2  from newdept;

    DEPTID DEPTNAME
---------- ------------------------------
        10 Marketing
        20 Sales
        30 Finance

SQL>

Notice how the processing shifted in the INSTEAD OF trigger when the department wasn't found in the dept table.


David Fitzjarrell

[oracle_guy]

This has been a very beneficial and pleasant interaction. All the code works just fine. I saw the code for my earlier question. I did it slightly differently yesterday and got to work. I have posted it there. Please critique it. Good to know the other way. Thanks David again for all the help.