Update through views: a possible approach

(In draft as at early February 2017.)

It is assumed the reader is familiar with the topic of update through views. 'View' is the usual term in the within database discussions, as for example in SQL. The ttm term is 'Virtual relvar'. Updating through views is discussed, for example, in both DTATRM and DBE. Most textbooks on the Relational Model or SQL discuss views and at least mention updating through views.

Background

Update through views is a regular discussion topic on the ttm forum. And a regular topic for Chris Date, co-author of The Third Manifesto. He has published many treatments, since particularly a series of articles (with David McGoveran) 1994 [published on dbdebunk]. Arising from that 1994 treatment also was the Principle Of Orthogonal Design, aimed at making update through views more tractable. McGoveran also continues to publish on the topic, also emphasising Orthogonal Design. The topic has also been much discussed on older forums, such as comp.database.theory.

There are a wide range of opinions on the desirability, possibility and practicability of update through views:

• Those who assert update is possible through all views, and the mechanisms merely need refining.
• Those who prescribe update should be possible through all views "that are theoretically updatable".

 [The quote is from Codd's 12 rules (1985), rule 6. The narrative for that rule includes a definition of 'theoretically updatable'.]

• Those who think some but not all views are updatable through, (which is not inconsistent with Codd 1985)

 and there is merit in a relational dbms being able to support that ability.
 Note that the SQL standard stipulates some capabilities, which have been somewhat implemented by vendors.

• Those who think that although some views might support being updated through, that is certainly not all of the views encountered in practice, so the uncertainty and continuing debate do not justify implementing a feature that can be only partial.
• Those who are adamant that (even if some views are updatable-through unproblematically), it is an unnecessary feature, and adds complexity both to the dbms and to developers/schema designers wantint to update the database.

 (It is true that update to base relvars is always sufficient, and necessarily so: the objective for update through views is to translate updates requested against views into updates against base.)

The "possible approach" propounded in this article is squarely aimed at getting some progress in the middle of this range. Specifically:

• The debate has tended to be polarised, with the most prominent advocates (Date particularly) at the "all views can be updated through" position.
• Date's continuing inability to 'solve' the problem (even to his own satisfaction) have lead to many being sceptical.
• The only major attempt at a partial implementation is SQL. It is beset with limitations and hard-to-explain behaviour. (And subtle differences between different vendors' implementations.) Such that most developers avoid the 'feature', and advise others to too. (See, for example, regular questions on StackOverflow.)
• Consequently there appears to be little thinking that acknowledges some views can be updated through, some not, and seeks to arrive at a precise characterisation of which and why. (And how, for those updates that can be supported.)