Mastering Oracle SQL

DML Statements on a Join View

A join view is a view based on a join. Special considerations apply when you issue a DML (INSERT, UPDATE, or DELETE) statement against a join view. Ever thought about what happens when you insert a row into a join view—which table does the row go into? And what happens when you delete a row from a join view—which table does it gets deleted from? This section deals with these questions.

To be modifiable, a join view must not contain any of the following:

• Hierarchical query clauses, such as START WITH or CONNECT BY

• GROUP BY or HAVING clauses

• Set operations, such as UNION, UNION ALL, INTERSECT, MINUS

• Aggregate functions, such as AVG, COUNT, MAX, MIN, SUM, and so forth

• The DISTINCT operator

• The ROWNUM pseudocolumn

A DML statement on a join view can modify only one base table of the view. Apart from these rules, therefore, a join view must also have one key preserved table to be modified.

Key-Preserved Tables

A key-preserved table is the most important requirement in order for a join view to be modifiable. In a join, a table is called a key-preserved table if its keys are preserved through the join—every key of the table can also be a key of the resultant join result set. Every primary key or unique key value in the base table must also be unique in the result set of the join. Let’s take an example to understand the concept of key preserved tables better.

               DESC EMPLOYEE Name Null? Type ------------------------------- -------- ---- EMP_ID NOT NULL NUMBER(4) ...