Re: Setting up updatable views ?

>> what would be an efficient taxonomy to list requisites for a system to support updateable views. <<

>From SQL FOR SMARTIES:

18.02. Updatable and Read-Only VIEWs

Unlike base tables, VIEWs are either updatable or read-only, but not both. INSERT, UPDATE, and DELETE operations are allowed on updatable VIEWs and base tables, subject to any other constraints. INSERT, UPDATE, and DELETE are not allowed on read-only VIEWs, but you can change their base tables, as you would expect.

An updatable VIEW is one that can have each of its rows associated with exactly one row in an underlying base table. When the VIEW is changed, the changes pass through the VIEW to that underlying base table unambiguously. Updatable VIEWs in Standard SQL are defined only for queries that meet these criteria

1. They are built on only one table,
2. No GROUP BY clause
3. No HAVING clause
4. No aggregate functions
5. No calculated columns
6. No UNION, INTERSECT or EXCEPT
7. No SELECT DISTINCT clause
8. Any columns excluded from the VIEW must be NULL-able or have a DEFAULT in the base table, so that a whole row can be constructed for insertion.

By implication, the VIEW must also contain a key of the table. In short, we are absolutely sure that each row in the VIEW maps back to one and only one row in the base table.

Some updating is handled by the CASCADE option in the referential integrity constraints on the base tables, not by the VIEW declaration.

The definition of updatability in Standard SQL is actually pretty limited, but very safe. The database system could look at information it has in the referential integrity constraints to widen the set of allowed updatable VIEWs. You will find that some implementations are now doing just that, but it is not common yet. The SQL standard definition of an updatable VIEW is actually a subset of the possible updatable VIEWs, and a very small subset at that. The major advantage of this definition is that it is based on syntax and not semantics. For example, these VIEWs are logically identical:

CREATE VIEW Foo1 -- updatable, has a key! AS SELECT *
     FROM Foobar
    WHERE x IN (1,2);

CREATE VIEW Foo2 -- not updateable!
AS SELECT *
     FROM Foobar
    WHERE x = 1
   UNION ALL
   SELECT *
     FROM Foobar
    WHERE x = 2;

But Foo1 is updateable and Foo2 is not. While I know of no formal proof, I suspect that determining if a complex query resolves to an updatable query for allowed sets of data values possible in the table is an NP-complete problem.

Without going into details, here is a list of types of queries that can yield updatable VIEWs, as taken from "VIEW Update Is Practical", (Goodman 1990):

1. Projection from a single table (Standard SQL)
2. Restriction/projection from a single table (Standard SQL)
3. UNION VIEWs
4. Set difference VIEWs
5. One-to-one joins
6. One-to-one outer joins
7. One-to-many joins
8. One-to-many outer joins
9. Many-to-many joins
10. Translated and coded fields

The CREATE TRIGGER mechanism for tables an action to be performed BEFORE, AFTER, or INSTEAD OF a regular INSERT, UPDATE, or DELETE to that table. It is possible for a user to write INSTEAD OF triggers on VIEWs, which catch the changes and route them to the base tables that make up the VIEW. The database designer has complete control over the way VIEWs are handled. Received on Fri Oct 05 2007 - 18:13:22 CEST