More detailed information is provided here to supplement the general definition of an updateable join view table (CREATE VIEW Statement (create_view_statement).

Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

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`Ti`.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`.- There is a key column or a foreign key column s' of a referential

`CONSTRAINT`definition relevant to`V`, which 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`in`Tj`, belonging to a referential`CONSTRAINT`definition`Rij`relevant to`V`, and`s`is the foreign key column of`Ti`assigned to`PK`-

`v`corresponds to a foreign key column`FK`in`Ti`, belonging to a referential`CONSTRAINT`definition`Rij`relevant to`V`, and`s`is the key column of`Tj`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 < .. < Tij`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 the`WITH CHECK OPTION`. If a view table`V`is derived from a view table`V'`and if`V'`was defined with the`WITH CHECK OPTION`, then V inherits the`CHECK OPTION`for the part of the qualification passed on by`V'`.