Oracle ORA-01779 Error: Causes and Solutions Complete Guide

ORA-01779: Cannot Modify a Column Which Maps to a Non Key-Preserved Table

ORA-01779 occurs when you attempt a DML operation (INSERT, UPDATE, or DELETE) through a join view or complex view on a column that belongs to a non key-preserved table. A key-preserved table is one whose primary key or unique key remains unique in the view's result set. Oracle enforces this restriction to protect data integrity — if a table's key is not preserved in the join, Oracle cannot guarantee a one-to-one mapping back to the base table row.

---

Top 3 Causes

1. Updating a Non Key-Preserved Table Column Through a Join View

In a 1:N join, the "many" side table is not key-preserved because its rows can appear multiple times in the result set.

-- Create a join view
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.salary, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- This FAILS with ORA-01779
-- departments is NOT key-preserved in a 1:N join
UPDATE emp_dept_view
SET    department_name = 'Finance'
WHERE  employee_id = 101;

-- Check which columns are updatable
SELECT column_name, updatable
FROM   user_updatable_columns
WHERE  table_name = 'EMP_DEPT_VIEW';

2. DML on Complex Views Without INSTEAD OF Triggers

Views containing GROUP BY, DISTINCT, UNION, or aggregate functions are inherently non-updatable without an INSTEAD OF trigger.

-- Complex view - DML not allowed without INSTEAD OF trigger
CREATE VIEW dept_salary_summary AS
SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM   employees
GROUP BY department_id;

-- Fails with ORA-01732 or ORA-01779
UPDATE dept_salary_summary
SET    avg_salary = 5000
WHERE  department_id = 10;

3. Attempting to Modify the "One" Side in a Multi-Table Join

Even in a simple two-table join, only the key-preserved table's columns are updatable directly. Developers often overlook which side of the join is key-preserved.

-- Only employees columns are updatable here
-- departments.department_name is NOT updatable
UPDATE emp_dept_view
SET    department_name = 'HR'  -- ORA-01779
WHERE  employee_id = 200;

-- This WORKS because employees is key-preserved
UPDATE emp_dept_view
SET    salary = 6000           -- OK
WHERE  employee_id = 200;

---

Quick Fix Solutions

Fix 1: DML directly on the base table

-- Bypass the view entirely
UPDATE departments
SET    department_name = 'Finance'
WHERE  department_id = 10;

Fix 2: Create an INSTEAD OF trigger

CREATE OR REPLACE TRIGGER trg_emp_dept_upd
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
    UPDATE employees
    SET    salary = :NEW.salary
    WHERE  employee_id = :OLD.employee_id;

    UPDATE departments
    SET    department_name = :NEW.department_name
    WHERE  department_name = :OLD.department_name;
END;
/

Fix 3: Use MERGE for complex updates

MERGE INTO departments d
USING (SELECT 10 AS dept_id, 'Finance' AS new_name FROM dual) src
ON (d.department_id = src.dept_id)
WHEN MATCHED THEN
    UPDATE SET d.department_name = src.new_name;

---

Prevention Tips

• Always query USER_UPDATABLE_COLUMNS after creating a join view to document which columns support DML before deploying to production. If critical columns show UPDATABLE = NO, plan for an INSTEAD OF trigger at design time.

• Design DML-intended views around key-preserved tables. Use single-table views whenever possible for write operations. If a join view is required, ensure the target table's primary key is included in the SELECT list so Oracle can verify key preservation.

---

Related Errors

Error Code	Description
ORA-01732	DML not allowed on this view (aggregate/GROUP BY views)
ORA-01733	Virtual columns not allowed here
ORA-01402	WITH CHECK OPTION violation
ORA-01031	Insufficient privileges on view DML

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.