CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
Dev
Dev

Reputation: 33

Cannot modify a column which maps to a non key-preserved table?

I am trying to run a simple update command to change the base table from this view which is emp. But I get this error message that I've never seen before and I don't quite understand what it's saying. I can provide any additional information if needed.

UPDATE emp_view_updt SET deptno = 30 WHERE empno = 7369;

enter image description here

Here is the definition of emp_view_updt:

CREATE VIEW emp_view_updt as SELECT e.empno, e.ename, e.job, d.deptno, DECODE (d.deptno, 10, 'New York', 20, 'Dallas', 30, 'Chicago', 40, 'Boston') as loc FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;

Here are the columns and data types: enter image description here

Here is what the view looks like: enter image description here

Upvotes: 0

Views: 1172

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

I believe you are missing a referential integrity constraint (and possibly a primary key). Make sure deptno is the primary key for dept, and add a foreign key constraint from emp to dept and the update should work. This allows the database to infer that each result from the view maps to a distinct row in dept.

update (select e.empno, e.ename, e.job, d.deptno
        from emp e inner join dept d on e.deptno = d.deptno)
set deptno = 30 where empno = 7369;

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

alter table dept add constraint dept_pk primary key (deptno);

alter table emp add constraint emp_dept_fk foreign key (deptno) references dept (deptno);

update (select e.empno, e.ename, e.job, d.deptno
        from emp e inner join dept d on e.deptno = d.deptno)
set deptno = 30 where empno = 7369;

1 row(s) updated.

https://livesql.oracle.com/apex/livesql/s/hm2ybpb8hnjoplqmdfoa4sb56

Upvotes: 1

Related Questions