Re: practical view updates

>> I must correct my statement: Mr.Date's view updates coverage in
"The Introduction to the Databases" is inspiring, but, nevertheless, his solution barely scratches the surface of the problem. <<

The SQL-92 standard is actually very conservative about what VIEWs are updatable. They have to be based on:

  (1) a SELECT statement on one and only one table; this can go thru several layers of VIEWs on top of VIEWs, however.

  (2) The VIEW must include all the columns of a key (i.e. a UNIQUE or PRIMARY KEY constraint) in the base table

  (3) All columns not shown in the VIEW must have default values or be NULL-able (if you think about it for a minute, this lets you construct and insert a complete row into the base table).

The whole idea is that an updatable VIEW looks and behaves pretty much like a base table, but slightly restricted as to what you can see. One row in the VIEW maps to exactly one row in the base table from which it is drawn.

However, other views are updatable and some vendors support more than the basic version given in the SQL-92 Standard. The VIEW must have an INSERT, UPDATE and DELETE rule under the covers which maps its rows back to a single row in the base table(s).

for practical information with the fastest reading time is Nathan Goodman in InfoDB. He discusses:

1. Projection from a single table
2. Selection/Projection from a single table
3. UNION VIEWs
4. Set Difference VIEWs
5. One-to-one joins
6. One-to-one outer joins
7. One-to-many outer joins
8. One-to-one outer joins
9. Many-to-many joins
10. Translated and coded fields

Codd, E. F.; THE RELATIONAL MODEL FOR DATABASE MANAGEMENT: VERSION TWO; "RV-6 VIEW Updating"; (ISBN 0-201-14192-2).

Date, C. J. & Hugh Darwen; RELATIONAL DATABASE WRITINGS -1989-1991; "Role of Functional Dependencies in Query Decomposition"; (ISBN 0-201-54303-6).

Date, C. J.; RELATIONAL DATABASE: SELECTED WRITINGS; "Updating VIEWs"; (ISBN 0-201-14196-5). Goodman, Nathan; INFODB Vol 5, No 2 (Summer 1990); "VIEW Update is Practical".

Umeshar, Dayal & P. A. Bernstein; ACM TRANSACTIONS ON DATABASE SYSTEMS Vol 7, No 3 (Dec 1982); "On the Correct Translation of Update Operations on Relational VIEWs".

Another feature, which is not used enough, is the WITH CHECK OPTION clause on a VIEW. It is a bit tricky, but if you would like I can post a section from SQL FOR SMARTIES on this feature. Received on Thu Oct 17 2002 - 22:12:18 CEST