Updateable Join View Table 

Updateable join view tables are extremely important. For this reason, more detailed information is provided here to supplement the general definition of an updateable join view table.

See also:

CREATE VIEW Statement (create_view_statement)

Explanation

Without limiting the generality, it is assumed that the definition of the join view table V in the FROM clause contains the base tables T1,...,Tn (n>1).

●     Let Ti and Tj be two base tables selected by V.
Let Rij be a referential CONSTRAINT definition of Ti and Tj in which Ti is the referencing table and Tj the referenced table (referenced_table).
Let PKj1,...,PKjm be the key columns of Tj.
Let FKi1,...,FKim be the corresponding foreign key columns of Ti.
The referential CONSTRAINT definition is relevant for V if the JOIN predicate (PKj1=FKi1 AND ... AND PKjm=FKjm) is part of the search condition of V.

●     Let Ti and Tj be two base tables selected by V and Rij a referential CONSTRAINT definition of Ti and Tj that is relevant for V.
Ti is the predecessor of Tj (Ti<Tj) if  Rij is the only referential CONSTRAINT definition of Ti and Tj that is relevant for V.

●     Let Rij be a referential CONSTRAINT definition that is relevant for V.
Rij defines a 1 : 1 relationship between Ti and Tj if the foreign key columns of Rij make up the key columns of Tj.

●     Let Rij be a referential CONSTRAINT definition that is relevant to V and s a key column of Tj or a foreign key column of this referential CONSTRAINT definition of Ti. The column s can be derived from V if exactly one of the following conditions is satisfied:
- s is a selected column of V.
- A key column or a foreign key column s' of a referential CONSTRAINT definition that is relevant to V exists that can be derived from V and the JOIN predicate s=s' is part of the search condition of V.

●     A column v of V corresponds to a column s of an underlying base table T if one of the following conditions is satisfied
- v is the i^th column of V and s is the i^th selected column of V.
- v corresponds to a key column PK of Tj in a referential CONSTRAINT definition Rij that is relevant to V and s is the foreign key column of Ti that is assigned to PK.
- v corresponds to a foreign key column FK of Ti of a referential CONSTRAINT definition Rij that is relevant to V and s is the key column of Tj that is assigned to FK.

A join view table V is updateable if the following conditions are satisfied:

●     Each base table Ti (1 <= i <= n) has a key defined by the user.

●     The database system must be able to determine a processing sequence for the underlying base tables; an order Ti1,...,Tin of the tables T1,...,Tn must exist such that j < k can be deduced from Tij<Tik. The columns of V from which the key columns of Ti1 can be derived make up the key of V. Ti1 is called the key table of V. The order of the tables does not have to be unique.

●     Starting with a row in the key table of V, it must be possible to assign each underlying base table exactly one row; that is, there is a sequence of tables Ti1,...,Tij for each table Tij (1 <= j <= n) such that Ti1 < ..  < T_ij is true.
This sequence is unique for each base table referred to by V.

●     It must be possible to derive the key columns and foreign key columns of all referential CONSTRAINT definitions relevant to V from the columns of V.

●     The join predicates needed to recognize the relevance of a referential CONSTRAINT definition must be specified in parts of the search condition defined with the WITH CHECK OPTION. If the view definition contains base tables only, this means that the view table must be defined with WITH CHECK OPTION. If a view table V is derived from a view table V' and if V' was defined with WITH CHECK OPTION, then V inherits the CHECK OPTION for the part of the qualification passed on by V'.