8 Top Rules to Write Updatable-view

Here’re the eight top rules for SQL view to be updatable. 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.

Updatable Views

  • 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 unambiguously through the VIEW to that underlying base table.
  • Updatable VIEWs in Standard SQL are defined only for queries that meet these criteria:
Advertisements

Rules for SQL view to be updatable

  1. 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 fairly limited, but very safe.
  • The database system could look at the 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.

SQL Query for Updatable-view 

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

SQL Query for Non-updatable View

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

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.