Creating a View

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 SELECT statement, 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.

Creating a View for SELECT

Many views combine data from multiple tables or other views. A view based on multiple tables or other views can be read, but not updated. For example, the following statement creates a read-only view, PHONE_LIST, because it joins two tables, EMPLOYEE, and DEPARTMENT:

EXEC SQL
CREATE VIEW PHONE_LIST AS
SELECT EMP_NO, FIRST_NAME, LAST_NAME, LOCATION, PHONE_NO
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO;
EXEC SQL
COMMIT;

Important:
Only a view’s creator initially has access to it. To assign read access to others, use GRANT. For more information about GRANT, see “Planning Security” inthe Data Definition Guide.

Creating a View for update

An updatable view is one that enables privileged users to insert, update, and delete information in the view’s base table. To be updatable, a view must meet the following conditions:

It derives its columns from a single table or updatable view.
It does not define a self-join of the base table.
It does not reference columns derived from arithmetic expressions.
The view’s SELECT statement does not contain:

A WHERE clause that uses the DISTINCT predicate
A HAVING clause
Functions
Nested queries
Stored procedures

In the following view, HIGH_CITIES is an updatable view. It selects all cities in the CITIES table with altitudes greater than or equal to a half mile.

EXEC SQL
CREATE VIEW HIGH_CITIES AS
SELECT CITY, COUNTRY_NAME, ALTITUDE FROM CITIES
WHERE ALTITUDE >= 2640;
EXEC SQL
COMMIT;

Users who have INSERT and UPDATE privileges for this view can change rows in or add new rows to the view’s underlying table, CITIES. They can even insert or update rows that cannot be displayed by the HIGH_CITIES view. The following INSERT adds a record for Santa Cruz, California, altitude 23 feet, to the CITIES table:

EXEC SQL
INSERT INTO HIGH_CITIES (CITY, COUNTRY_NAME, ALTITUDE)
VALUES ('Santa Cruz', 'United States', '23');

To restrict inserts and updates through a view to only those rows that can be selected by the view, use the WITH CHECK OPTION in the view definition. For example, the following statement defines the view, HIGH_CITIES, to use the WITH CHECK OPTION. Users with INSERT and UPDATE privileges will be able to enter rows only for cities with altitudes greater than or equal to a half mile.

EXEC SQL
CREATE VIEW HIGH_CITIES AS
SELECT CITY, COUNTRY_NAME, ALTITUDE FROM CITIES
WHERE ALTITUDE > 2640 WITH CHECK OPTION;

Advance To:

Creating an Index