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)

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'.