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.
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 EMPLOYEEName Null? Type ------------------------------- -------- ---- EMP_ID NOT NULL NUMBER(4) ...