Databases Reference
In-Depth Information
impenetrable in this area than it usually is. The following extract (which is quoted verbatim
from SQL:2003, the 2003 version of the standard) gives some idea of the complexities involved:
[The] <query expression>
QE1
is
updatable
if and only if for every <query expression> or <query
specification>
QE2
that is simply contained in
QE1
:
a)
QE1
contains
QE2
without an intervening <query expression body> that specifies UNION
DISTINCT, EXCEPT ALL, or EXCEPT DISTINCT.
b)
If
QE1
simply contains a <query expression body>
QEB
that specifies UNION ALL, then:
i)
QEB
immediately contains a <query expression>
LO
and a <query term>
RO
such that no leaf
generally underlying table of
LO
is also a leaf generally underlying table of
RO
.
ii)
For every column of
QEB,
the underlying columns in the tables identified by
LO
and
RO,
respectively, are either both updatable or not updatable.
c)
QE1
contains
QE2
without an intervening <query term> that specifies INTERSECT.
d)
QE2
is updatable.
Here's my own gloss on the foregoing extract:
First of all, observe that the extract in question represents just one of the many rules that
have to be taken in combination in order to determine whether some given view is
updatable in SQL.
The rules in question aren't all given in one place but are scattered over many different
portions of the standard.
All of those rules rely on a variety of additional concepts and constructs—e.g., updatable
columns, leaf generally underlying tables, <query term>s—that are defined in turn in still
further portions of the standard.
What's more, the rule as defined by this particular extract doesn't even seem to make
sense. To be specific, the opening sentence says, in effect, that four conditions a), b), c), and d)
have to be satisfied “for every ...
QE2
that is simply contained in
QE1
”—yet item b) in particular
doesn't even mention any such
QE2
(?).
