GENERAL Rule for updatable join views - Oracle Views, Sequences, and Synonyms

Discussion

• Nirja Shah   -Posted on 29 Sep 15
  - An updatable join view (also referred to as a modifiable join view) is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.
  
  - The rules for updatable join views are as follows.
  
  - Views that meet these criteria are said to be inherently updatable.
  
  General Rule
  Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.
  
  UPDATE Rule
  - All updatable columns of a join view must map to columns of a key-preserved table.
  - If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable.
  
  DELETE Rule
  - Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join.
  - The key preserved table can be repeated in the FROM clause. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
  
  INSERT Rule
  - An INSERT statement must not explicitly or implicitly refer to the columns of a non-key-preserved table.
  - If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.

➨ Post your comment / Share knowledge