Creating a View
Go Up to Creating Metadata
A view is a virtual table that is based on a subset of one or more actual tables in a database. Views are used to:
- Restrict user access to data by presenting only a subset of available data.
- Rearrange and present data from two or more tables in a manner especially useful to the program.
Unlike a table, a view is not stored in the database as raw data. Instead, when a view is created, the definition of the view is stored in the database. When a program uses the view, InterBase reads the view definition and quickly generates the output as if it were a table.
To make a view, use the following CREATE VIEW syntax:
EXEC SQL CREATE VIEW name [(view_col [, view_col ...)] AS <select> [WITH CHECK OPTION];
The name of the view, <name>, must be unique within the database.
To give each column displayed in the view its own name, independent of its column name in an underlying table, enclose a list of <view_col> parameters in parentheses. Each column of data returned by the view SELECT statement is assigned sequentially to a corresponding view column name. If a list of view column names is omitted, column names are assigned directly from the underlying table.
Listing independent names for columns in a view ensures that the appearance of a view does not change if its underlying table structures are modified.
- Note: A view column name must be provided for each column of data returned by the view
SELECTstatement, or else no view column names should be specified.
The <select> clause is a standard SELECT statement that specifies the selection criteria for rows to include in the view. A SELECT in a view cannot include an ORDER BY clause. In DSQL, it cannot include a UNION clause.
The optional WITH CHECK OPTION restricts inserts, updates, and deletes in a view that can be updated.
To create a read-only view, a creator of the view must have SELECT privilege for the table or tables underlying the view. To create a view for update requires ALL privilege for the table or tables underlying the view. For more information about SQL privileges, see “Planning Security” in the Data Definition Guide.