Oracle ORA-01445 Error: Causes and Solutions Complete Guide

ORA-01445: Cannot Select ROWID from a Join View Without a Key-Preserved Table

ORA-01445 is thrown when you attempt to select the ROWID pseudo-column from a join view that lacks a key-preserved table. A key-preserved table is one whose primary or unique key ensures a one-to-one mapping between each row in the view and a row in the underlying base table. Oracle enforces this rule to maintain data integrity when performing row-level operations on join views.

---

Top 3 Causes

1. Directly Selecting ROWID from a Join View

The most common trigger is querying ROWID from a view built on two or more joined tables without ensuring key preservation.

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

-- This will throw ORA-01445
SELECT ROWID FROM emp_dept_view;

-- Fix: expose the key-preserved table's ROWID explicitly
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.ROWID AS emp_rowid,
       e.employee_id,
       e.last_name,
       d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Now safe to use
SELECT emp_rowid FROM emp_dept_view;

2. Running DML on a Join View Without PK Columns

When a view's SELECT list omits the primary key of a joined table, Oracle cannot guarantee a unique row mapping, making DML operations unsafe.

-- PK (department_id) is missing from the SELECT list
CREATE OR REPLACE VIEW bad_view AS
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Triggers ORA-01445
UPDATE bad_view SET department_name = 'Sales' WHERE last_name = 'King';

-- Fix: include PKs of both tables in the view
CREATE OR REPLACE VIEW good_view AS
SELECT e.employee_id,      -- PK of employees
       e.last_name,
       e.department_id,    -- PK of departments
       d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Check updatability
SELECT column_name, updatable
FROM user_updatable_columns
WHERE table_name = 'GOOD_VIEW';

3. DML on Complex Views Without INSTEAD OF Triggers

Views containing GROUP BY, DISTINCT, UNION, or aggregate functions can never have a key-preserved table. Attempting DML without an INSTEAD OF trigger will fail.

-- Aggregated view — inherently non-key-preserved
CREATE OR REPLACE VIEW dept_summary AS
SELECT d.department_id, d.department_name, AVG(e.salary) AS avg_sal
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- Fix: create an INSTEAD OF trigger
CREATE OR REPLACE TRIGGER trg_dept_summary_upd
INSTEAD OF UPDATE ON dept_summary
FOR EACH ROW
BEGIN
    UPDATE departments
    SET department_name = :NEW.department_name
    WHERE department_id = :OLD.department_id;
END;
/

-- Now this works cleanly
UPDATE dept_summary
SET department_name = 'Corporate Finance'
WHERE department_id = 100;

---

Quick Fix Solutions

• Expose the base table ROWID as a named column in your view definition.
• Include all primary key columns of every joined table in the view's SELECT list.
• Use USER_UPDATABLE_COLUMNS to diagnose which columns are safe for DML before writing application code.
• Create INSTEAD OF triggers for views that are inherently complex or aggregated.

-- Quick diagnostic
SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'YOUR_VIEW_NAME';

---

Prevention Tips

1. Design views with DML intent in mind. Always decide upfront whether a view is read-only or needs to support inserts, updates, and deletes. Include primary keys for all base tables in the SELECT list of any updatable join view, and validate with USER_UPDATABLE_COLUMNS immediately after creation.

2. Standardize INSTEAD OF triggers for complex views. For any view involving aggregation, set operations, or multiple outer joins, treat the INSTEAD OF trigger as a mandatory design artifact — not an afterthought. Document this in your team's view design guidelines to prevent runtime surprises in production.

---

Related Errors

Error Code	Description
ORA-01779	Cannot modify a column mapped to a non key-preserved table
ORA-01732	DML not legal on this view (aggregation/DISTINCT present)
ORA-01736	Only simple views support INSERT/UPDATE/DELETE

---

📖 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.