How to Fix ORA-01779: Cannot Modify a Column Which Maps to a Non Key-Preserved Table in Oracle

ORA-01779 is raised when an UPDATE through a join view, or an inline view, changes a column of a table whose rows are not key-preserved: one row of that table can appear in several rows of the view. Classic examples update a department's name through an employee-department view. Oracle AI Database 26ai is more permissive than earlier releases, as this test shows, but the underlying problem, one table row behind several view rows, still decides what works.

Reproduce the Problem

Two employees work in one department, and the view joins them:

Example:

create table demo_dept5 (dept_id number primary key, dept_name varchar2(30));
create table demo_emp5  (emp_id number primary key, name varchar2(30), dept_id number references demo_dept5);
insert into demo_dept5 values (10, 'Sales');
insert into demo_emp5 values (1, 'Mia', 10);
insert into demo_emp5 values (2, 'Leo', 10);
commit;
create view demo_emp_dept_v as
select e.emp_id, e.name, d.dept_id, d.dept_name
from   demo_emp5 e join demo_dept5 d on d.dept_id = e.dept_id;

-- DEPT_NAME comes from the non key-preserved table
select column_name, updatable from user_updatable_columns where table_name = 'DEMO_EMP_DEPT_V';

update demo_emp_dept_v set dept_name = 'Retail' where emp_id = 1;    -- one employee: one department row
update demo_emp_dept_v set dept_name = name || ' team';             -- two values for the same row
rollback;

Output (Oracle AI Database 26ai):

Table DEMO_DEPT5 created.

Table DEMO_EMP5 created.

1 row inserted.

1 row inserted.

1 row inserted.

Commit complete.

View DEMO_EMP_DEPT_V created.

COLUMN_NAME    UPDATABLE
______________ ____________
EMP_ID         YES
NAME           YES
DEPT_ID        NO
DEPT_NAME      NO

1 row updated.

Error starting at line : 15
In command -
update demo_emp_dept_v set dept_name = name || ' team'
Error at Command Line : 15 Column : 8
Error report -
SQL Error: ORA-30926: The operation attempted to update the same row (rowid:
'AAAYDJAAAAAASC2AAA') twice.

Rollback complete.

• USER_UPDATABLE_COLUMNS marks DEPT_ID and DEPT_NAME as not updatable: DEMO_DEPT5 is not key-preserved in the view.
• Even so, 26ai updated the department name for one employee, since that touches the department row once. Earlier releases refuse this update with ORA-01779.
• Updating the name for both employees with different values would change the same department row twice, and fails with ORA-30926.

Fix: Update the Base Table

Example:

-- update the department table itself
update demo_dept5 set dept_name = 'Retail' where dept_id = 10;
-- columns of the key-preserved table update fine through the view
update demo_emp_dept_v set name = 'Mia Lopez' where emp_id = 1;
select * from demo_emp_dept_v order by emp_id;

Output:

1 row updated.

1 row updated.

   EMP_ID NAME            DEPT_ID DEPT_NAME
_________ ____________ __________ ____________
        1 Mia Lopez            10 Retail
        2 Leo                  10 Retail

Department data is updated in its own table, once, and employee columns, which are key-preserved, still update through the view.

Things to Know

• A table is key-preserved in a join when its primary key is also unique in the join result, as the employee table is here.
• For views that users must update freely, an INSTEAD OF trigger decides what each change does to the base tables.
• MERGE is often the clearest way to update one table from another, instead of an update through a join.

Related Guides

• How to Write INSTEAD OF Triggers in Oracle
• How to Fix ORA-30926: Unable to Get a Stable Set of Rows in the Source Tables in Oracle

Conclusion

ORA-01779 comes from updating a table through a join where its rows repeat. Check USER_UPDATABLE_COLUMNS, update such tables directly or with MERGE, and use INSTEAD OF triggers when a join view itself must accept the changes.