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
NULLprimary key value
- an insert to the view generates a
- primary keys may not be
NULL, so the insert is not allowed
- If a base table primary key does not appear in a view,
- 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
WHEREclause- 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 ];