12.2 DML Operations on Views & View Constraints

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

12.2 DML Operations on Views & View Constraints

Key Takeaways Explain This Topic AI

DML operations through a view directly mutate base table data; a view does not hold data independently.

In a joined (complex) view, DML can ONLY modify columns belonging to the Key-Preserved Table (a table whose primary/unique key remains unique after the join).

Modifying non-key-preserved columns in a join view causes runtime error ORA-01779: cannot modify a column which maps to a non key-preserved table.

The WITH CHECK OPTION clause prevents INSERT and UPDATE operations from modifying rows in a way that violates the view's WHERE clause, raising ORA-01402 upon violation.

The WITH READ ONLY clause completely disallows all INSERT, UPDATE, and DELETE operations through the view, raising ORA-42399 upon any DML attempt.

Last updated: August 2026

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:

DELETE Operations: Always permitted on simple views (unless restricted by WITH READ ONLY ). The target row is permanently deleted from the physical base table.

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 .

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

Single Table Target: Any single DML statement executed against a join view can modify columns from only one underlying base table at a time.

Key-Preservation Mandate: DML ( INSERT or UPDATE ) can modify columns belonging only to the key-preserved table .

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

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

Feature / Operation Standard View (No Option) WITH CHECK OPTION WITH READ ONLY
SELECT Queries Allowed Allowed Allowed
DELETE Matching Rows Allowed Allowed Blocked (ORA-42399)
UPDATE View Columns (Compliant) Allowed Allowed Blocked (ORA-42399)
UPDATE View Columns (Non-Compliant) Allowed (row vanishes from view) Blocked (ORA-01402) Blocked (ORA-42399)
INSERT Rows (Compliant) Allowed Allowed Blocked (ORA-42399)
INSERT Rows (Non-Compliant) Allowed (row invisible in view) Blocked (ORA-01402) Blocked (ORA-42399)
Primary Purpose Simple projection / reporting Enforce predicate integrity Absolute data lockdown
Oracle 1Z0-071 View Error Code Reference Matrix

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

Error Code Error Message Text Exact Root Cause on 1Z0-071

ORA-01402 view WITH CHECK OPTION where-clause violation Attempted INSERT or UPDATE through a WITH CHECK OPTION view with values failing the WHERE condition.

ORA-42399 cannot perform a DML operation on a read-only view Attempted INSERT , UPDATE , or DELETE through a WITH READ ONLY view.

ORA-01779 cannot modify a column which maps to a non key-preserved table Attempted INSERT or UPDATE on columns of a table in a join view that does not have a 1:1 key preservation.

ORA-01732 data manipulation operation not legal on this view Attempted DML on a view containing GROUP BY , aggregate functions, DISTINCT , ROWNUM , or set operators.

ORA-01733 virtual column not allowed here Attempted INSERT or UPDATE on a calculated expression or virtual column in a view.

ORA-01400 cannot insert NULL into (...) Attempted INSERT through a view that omits a NOT NULL base table column lacking a DEFAULT clause.