12.2 DML Operations on Views & View Constraints

12.2 DML Operations on Views & View Constraints

Because views are virtual tables representing stored queries rather than physical storage containers, performing Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE) through a view requires the Oracle Database engine to translate operations directly into modifications against the underlying base table(s).

While simple views generally permit full DML transparency, complex views and views with integrity constraints are governed by rigorous relational rules. Mastering the Key-Preserved Table concept, DML prohibitions, and the WITH CHECK OPTION / WITH READ ONLY clauses is a core requirement for passing the Oracle 1Z0-071 examination.

---

Fundamental Rules for DML on Simple Views

For a single-table simple view, DML statements operate seamlessly against the underlying base table under the following conditions:

1. DELETE Operations: Always permitted on simple views (unless restricted by WITH READ ONLY). The target row is permanently deleted from the physical base table.
2. UPDATE Operations: Permitted on any column mapped directly to a base table column. Updating virtual or calculated columns (such as salary * 12) is strictly illegal and raises ORA-01733: virtual column not allowed here.
3. INSERT Operations: Permitted only if all columns defined with a NOT NULL constraint in the physical base table (that lack a default value) are included in the view's projection list. If a base table requires a non-null column that the view omits, an INSERT through the view fails with ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE"."COLUMN").

---

The Key-Preserved Table Concept in Join Views

When a view joins two or more base tables, executing DML statements becomes subject to the Key-Preserved Table rule.

Definition of a Key-Preserved Table

A table in a join view is defined as a Key-Preserved Table if its primary key or unique key continues to be uniquely identifiable in the result set produced by the view. In relational terms, there is a strict 1:1 relationship between rows in the view and rows in that specific base table.

+-------------------------------------------------------------------------+
|                   KEY-PRESERVED TABLE IN A JOIN VIEW                    |
+-------------------------------------------------------------------------+
|                                                                         |
|  VIEW: emp_dept_v                                                       |
|  SELECT e.employee_id, e.last_name, e.salary, e.department_id,          |
|         d.department_name                                               |
|  FROM employees e                                                       |
|  JOIN departments d ON e.department_id = d.department_id;               |
|                                                                         |
|  +-------------------------------------------------------------------+  |
|  | EMP_ID (PK) | LAST_NAME | SALARY | DEPT_ID | DEPT_NAME (d.name)   |  |
|  +-------------+-----------+--------+---------+----------------------+  |
|  | 101         | Kochhar   | 17000  | 90      | Executive            |  |
|  | 102         | De Haan   | 17000  | 90      | Executive            |  |
|  | 103         | Hunold    | 9000   | 60      | IT                   |  |
|  +-------------------------------------------------------------------+  |
|                                                                         |
|  ANALYSIS OF BASE TABLES:                                               |
|  1. EMPLOYEES Table:                                                    |
|     - Primary Key: EMPLOYEE_ID                                          |
|     - Every row in the view represents exactly ONE unique employee.     |
|     - EMPLOYEES IS A KEY-PRESERVED TABLE.                               |
|     - Result: Columns (last_name, salary, dept_id) ARE UPDATABLE!       |
|                                                                         |
|  2. DEPARTMENTS Table:                                                  |
|     - Primary Key: DEPARTMENT_ID                                        |
|     - Department 90 ('Executive') is duplicated across multiple rows.   |
|     - DEPARTMENTS IS NOT A KEY-PRESERVED TABLE.                         |
|     - Result: Column (department_name) CANNOT BE MODIFIED!              |
|                                                                         |
+-------------------------------------------------------------------------+

Fundamental DML Rules on Joined Views

1. Single Table Target: Any single DML statement executed against a join view can modify columns from only one underlying base table at a time.
2. Key-Preservation Mandate: DML (INSERT or UPDATE) can modify columns belonging only to the key-preserved table.
3. Attempting DML on Non-Key-Preserved Columns (ORA-01779): If a user attempts to update a column originating from a non-key-preserved table, Oracle halts the transaction:

   -- ILLEGAL: Modifying department_name (belongs to non-key-preserved DEPARTMENTS)
   UPDATE emp_dept_v
   SET department_name = 'Administration'
   WHERE employee_id = 101;
   -- ORA-01779: cannot modify a column which maps to a non key-preserved table
   

4. Legal Update on Key-Preserved Columns:

   -- LEGAL: Modifying salary (belongs to key-preserved EMPLOYEES table)
   UPDATE emp_dept_v
   SET salary = 18000
   WHERE employee_id = 101;
   -- 1 row updated.
   

5. DELETE Operations in Join Views: DELETE statements remove rows from the key-preserved table. If a join view has only one key-preserved table, DELETE FROM view_name WHERE ... deletes the matching rows from that key-preserved base table while leaving the non-key-preserved table untouched.

Verifying Column Updatability with USER_UPDATABLE_COLUMNS

You can query the Oracle data dictionary view USER_UPDATABLE_COLUMNS to verify which columns of a complex view support DML:

SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'EMP_DEPT_V';

---

Absolute Prohibitions on View DML

Oracle completely prohibits DML operations on any view (simple or complex) that contains any of the following SQL constructs:

+-------------------------------------------------------------------------+
|                   VIEW DML PROHIBITION CHECKLIST                        |
+-------------------------------------------------------------------------+
|                                                                         |
|  A view CANNOT perform DML (INSERT / UPDATE / DELETE) if it contains:  |
|                                                                         |
|  [X] GROUP BY clause                                                    |
|  [X] Aggregate / Group Functions (SUM, AVG, MIN, MAX, COUNT, etc.)      |
|  [X] DISTINCT operator                                                  |
|  [X] ROWNUM pseudo-column                                               |
|  [X] Set Operators (UNION, UNION ALL, INTERSECT, MINUS)                 |
|  [X] WITH READ ONLY constraint                                          |
|                                                                         |
|  Attempting DML raises:                                                 |
|  --> ORA-01732: data manipulation operation not legal on this view      |
|                                                                         |
+-------------------------------------------------------------------------+

Note on INSTEAD OF Triggers: While standard DML is prohibited on such views, PL/SQL developers can create an INSTEAD OF trigger on the view to custom-handle DML operations. However, for pure Oracle SQL (1Z0-071 scope), these operations are inherently prohibited.

---

The WITH CHECK OPTION Constraint

When a view is created with a WHERE clause, a user performing INSERT or UPDATE operations through the view might modify data such that the affected rows no longer meet the view's filter criteria (effectively causing the rows to "vanish" from the view).

To preserve data integrity, the WITH CHECK OPTION clause guarantees that any INSERT or UPDATE performed through the view must conform to the view's WHERE clause condition.

Syntax

CREATE OR REPLACE VIEW it_programmers_v AS
SELECT employee_id, first_name, last_name, job_id, salary, department_id
FROM employees
WHERE department_id = 60
WITH CHECK OPTION CONSTRAINT it_prog_v_ck;

Mechanics and Error Enforcement (ORA-01402)

1. Violating UPDATE Statement

If an update attempts to change department_id to a value other than 60:

-- ILLEGAL: Violates WHERE department_id = 60
UPDATE it_programmers_v
SET department_id = 80
WHERE employee_id = 103;
-- ORA-01402: view WITH CHECK OPTION where-clause violation

2. Violating INSERT Statement

If an insert attempts to create an employee with department_id = 50:

-- ILLEGAL: Violates WHERE department_id = 60
INSERT INTO it_programmers_v (employee_id, last_name, job_id, salary, department_id)
VALUES (999, 'Miller', 'IT_PROG', 6500, 50);
-- ORA-01402: view WITH CHECK OPTION where-clause violation

3. DELETE Statements under WITH CHECK OPTION

DELETE statements through a WITH CHECK OPTION view are fully permitted for rows that currently satisfy the view's WHERE clause. Since deleting a row removes it from the table entirely, it does not violate the predicate constraint.

---

The WITH READ ONLY Constraint

The WITH READ ONLY clause enforces complete immutability. It ensures that no user can execute INSERT, UPDATE, or DELETE statements against the view under any circumstances.

Syntax

CREATE OR REPLACE VIEW executive_ro_v AS
SELECT employee_id, last_name, job_id, salary, department_id
FROM employees
WHERE department_id = 90
WITH READ ONLY CONSTRAINT exec_ro_ck;

Error Enforcement (ORA-42399)

Attempting any DML operation on a read-only view results in immediate rejection:

DELETE FROM executive_ro_v WHERE employee_id = 100;
-- ORA-42399: cannot perform a DML operation on a read-only view

UPDATE executive_ro_v SET salary = salary * 1.05;
-- ORA-42399: cannot perform a DML operation on a read-only view

---

Comparison: WITH CHECK OPTION vs. WITH READ ONLY

---

Oracle 1Z0-071 View Error Code Reference Matrix

Recognizing Oracle error codes and their exact trigger conditions is heavily tested on the certification exam: