Show TOC

Background documentationCREATE VIEW Statement (create_view_statement)

 

The CREATE VIEW statement (create_view_statement) defines a view table.

View table

A view table is a view of an existing table: Parts of the table are hidden and other parts remain visible. You can use VIEW tables to truncate longer SELECT statements. You can use view tables to hide unimportant or confidential data.

A view table never actually exists physically but is formed from the rows of the underlying base table(s) when the view table is specified in an SQL statement.

Structure

Syntax Syntax

  1. <create_view_statement> ::=
  CREATE [OR REPLACE] VIEW <table_name> [(<alias_name>,...)]
    AS <query_expression> [WITH CHECK OPTION]
End of the code.
Examples

SQL Tutorial, View Tables

Concepts of the Database System, Objects in the Schema HOTEL

Explanation

If no schema is specified in the view table name (table_name), the current schema is assumed implicitly. The view table name must not be identical with the name of a table already existing in the schema.

When the CREATE VIEW statement is executed, metadata describing the view table is stored in the database catalog.

The view table is always identical to the table you would obtain as the result of the QUERY expression (query_expression). The QUERY expression must not contain a parameter specification or a TOP syntax element. The QUERY expression must not reference a temporary table or a results table name.

The table expressions of the QUERY specification in the QUERY expression of the CREATE VIEW statement must not contain a QUERY expression.

If a column selected by the QUERY expression is of the data type LOB, the FROM clause must contain precisely one table name with precisely one underlying base table.

Join View Table

A join view table is a view table where the FROM clause contains more than one table or one join table.

Complex View Table

A view table is a complex view table if it satisfies one of the following conditions:

  • The definition of the view table contains DISTINCT, GROUP BY or HAVING.

  • The CREATE VIEW statement contains EXCEPT, INTERSECT or UNION.

  • The search condition contained in the QUERY expression of the CREATE VIEW statement contains a subquery.

  • The CREATE VIEW statement contains an outer join, that is an OUTER JOIN indicator (outer_join_indicator) in a JOIN predicate of the search condition.

Updateable View Table

A view table is deemed updateable if it is not a complex view table and if it is not based on a complex view table.

Updateable Join View Table

Caution Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

An updateable join view table is an updateable view table for which the following conditions must also be fulfilled:

  • Each base table on which the view table is based must have a key defined by the user.

  • Referential CONSTRAINT definitions must exist between the base tables on which the view table is based.

  • Exactly one of the underlying base tables for the view table must exist that is not a referenced table (referenced_table) of a referential CONSTRAINT definition for a different base table of the view table. This table must be the key table of the view table.

  • For each underlying base table of the view table, there must be a sequence of referential CONSTRAINT definitions enabling the respective base table to be accessed from the key table.

  • The referential CONSTRAINT definitions must be reflected as a JOIN predicate in the search condition of the CREATE VIEW statement. In other words, the condition "key column = foreign key column" must exist for every column in each referential CONSTRAINT definition.

  • The CREATE VIEW statement must contain either the primary key or foreign key column from each referential CONSTRAINT definition as the selected column, but cannot contain both.

  • The view table must be defined with WITH CHECK OPTION.

For further explanations, see Updateable Join View Table.

Privileges

The user must have the SELECT privilege for all columns occurring in the view definition. The database user is the owner of the view table and has the SELECT privilege for this at the very least. The user may assign the SELECT privilege to others for any columns in the view table derived from columns for which he or she is authorized to do this.

The database user only has the INSERT, UPDATE and DELETE privileges if he or she has the relevant privileges for the underlying tables of the view table and join view table and if the view table and the join view table are updateable. The user may only grant these privileges to others if he or she is authorized to allocate the relevant privilege for all of the underlying tables of the view table and join view table.

For further explanations, see INSERT/UPDATE/DELETE Privilege for Owners of View Tables and Privilege Specification (priv_spec).

OR REPLACE

If OR REPLACE is not specified, the table_name cannot be identical to the name of an existing view table.

If OR REPLACE is specified, however, the table name may be identical to the name of an existing view table. In this case, the new definition for the existing view table replaces the existing definition. The database system then attempts to adapt privileges granted for the existing view table to the new view definition. The privileges for the view table usually remain unchanged. Privileges are only removed implicitly if conflicts occur that cannot be resolved by the database system. If there are major discrepancies between the two view definitions, the CREATE VIEW statement can fail in the following case: The CREATE VIEW statement for the view table that is based on the existing view table can no longer be executed for the new view definition without an error occurring.

Alias Names (alias_name)

The column names of the view table must be unique. If this is not the case in the results table generated by the QUERY expression, alias names must be specified that define the column names in the view table. The number of alias names must be equal to the number of columns in the results table generated by the QUERY expression. If no alias names are specified, the column names of the results table generated by the QUERY expression are copied to the view table. The column descriptions for the view table are taken from the corresponding columns in the QUERY expression. The FROM clause of the QUERY expression can contain one or more tables.

WITH CHECK OPTION

If the CREATE VIEW statement contains a WITH CHECK OPTION, the owner of the view table must have been granted the INSERT, UPDATE, or DELETE privilege for the view table.

If the WITH CHECK OPTION is specified and an INSERT or UPDATE statement is issued for the view table, rows can only be created that can subsequently be selected via the view table. In other words, the search condition for the view table must be fulfilled for the resulting rows.

The CHECK OPTION is inherited. This means that, if a view table V has been defined with the WITH CHECK OPTION and V occurs in the FROM clause of an updateable view table V1, only those rows that can be selected using V may be inserted or altered using V1.