View Tables


Views restructure table columns and data types without changes to the underlying database design.

  • may want to have different views for different departments/roles
  • allow us to store scripts that can be used for reporting
  • provide valuable joins and information for other users without requiring them to know how to join, or even what tables to add to the query

A view table is a table name associated with a SELECT statement, called the view query.

Creating Views

The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names.

  • If column names are not specified,
    • column names are the same as in the view query result table

CREATE VIEW Statement Syntax

CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement;

Example

CREATE VIEW ProductsAndRatings AS
 
SELECT ToyProducts.ProductID, ToyProducts.ProductName,
ToyProducts.ProductListPrice, ToyStarRatings.StarRating, 
ToyStarRatings.CT_Rates
 
FROM ToyProducts INNER JOIN
 
ToyStarRatings ON ToyProducts.ProductID = ToyStarRatings.ProductID
  • Can use existing views to create new views

Querying Views

Base tables are always source tables, created as tables rather than as views

A table specified in the view query’s FROM clause is called a base table.

  • view table data is not normally stored
    • instead, when a view table appears in a SQL statement
    • the view query is merged with the SQL query
    • database executes the merged query against base tables

A materialized view is a view for which data is stored at all times.

  • Whenever a base table changes,
    • the corresponding view tables can also change,
    • so materialized views must be refreshed
  • To avoid the overhead of refreshing views,
    • MySQL and many other databases do not support materialized views

Advantages of Views

  • Protect sensitive data
    • view can exclude sensitive columns but include all other columns
    • Authorizing users and programmers to access the view but not the underlying table protects the sensitive data
  • Save complex queries
    • can reference the view without writing the SELECT statement
  • Save optimized queries

Inserting, Updating, and Deleting Views

Using views in INSERT, UPDATE, and DELETE statements is problematic:

  • Primary keys
    • If a base table primary key does not appear in a view,
      • an insert to the view generates a NULL primary key value
    • primary keys may not be NULL, so the insert is not allowed
  • Aggregate values
    • A view query may contain aggregate functions
    • One aggregate value corresponds to many base table values
    • An update or insert to the view may create a new aggregate value,
      • which must be converted to many base table values
    • The conversion is undefined,
      • so the insert or update is not allowed
  • Join views
    • In a join view, foreign keys of one base table may match primary keys of another
    • A delete from a view might delete
      • foreign key rows only,
      • or primary key rows only,
      • or both the primary and foreign key rows
    • The effect of the join view delete is undefined and therefore not allowed

WITH CHECK OPTION Clause

When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.

  • Instead, the database generates an error message that explains the violation

Background

  • Databases that allow view updates face one particularly bothersome behavior
  • A view insert or update may create a row that does not satisfy the view query WHERE clause
  • In this case, the inserted or updated row does not appear in the view table
  •  From the perspective of the database user, the insert or update appears to fail even though the base tables have changed

WITH CHECK OPTION Syntax

CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement
[ WITH CHECK OPTION ];