ALLBASE/SQL Reference Manual (36216-90216)

368 Chapter10
SQL Statements A - D
CREATE VIEW
ConstraintID is the optional name of the view check constraint.
Description
• A view definition with * in the select list generates a view that refers to all the columns
that exist in the base table(s)
at the time the view is
created
. Adding new columns to the base tables does not cause these columns to be
added to the view.
• A view is said to be updatable when you can use it in DELETE, UPDATE, or INSERT
statements to modify the base table. A view is updatable only if the query from which it
is derived matches the following updatability criteria:
• No DISTINCT, GROUP BY, orHAVING clause is specified in the outermost SELECT
clause, and no aggregate appears in its select list.
• The FROM clause specifies exactly one table, which must be an updatable table. See
"Updatability of Queries" in the "SQL Queries" chapter.
• To use INSERT and UPDATE statements through views, the select list in the view
definition must not contain any arithmetic expressions. It must contain only column
names.
•ForDELETE WHERE CURRENT and UPDATE WHERE CURRENT statements operating
on cursors defined with views, the view definition must not contain subqueries.
• For noncursor UPDATE, DELETE, and INSERT statements, the view definition must
not contain any subqueries which contain in their FROM clauses a table reference to
the same table as the outermost FROM clause.
• You cannot define an index on a view or alter a view.
• You cannot use host variables, local variables, procedure parameters, or dynamic
parameters in the CREATE VIEW statement.
• Creating a view causes a section to be stored in the system catalog. A description of the
section appears in the SYSTEM.SECTION view.
• If you use the CREATE VIEW statement within the CREATE SCHEMA statement, the
default owner of the view is the schema's AuthorizationName.
• When you create a view, an entry containing the SELECT statement in the view
definition is stored in the SYSTEM.VIEWDEF view in the system catalog. The view's
name is stored in SYSTEM.TABLE, and the description of its columns appears in
SYSTEM.COLUMN.
• If you use the CREATE VIEW statement with a CREATE PROCEDURE statement, the
default owner is the procedure owner.
• Any attempt to write through a view defined having a WITH CHECK OPTION must
satisfy any conditions specified in the query specification. All underlying view
definitions are also checked. Any constraints in the table on which the view is based are
also checked.
• View check constraints are not deferrable.
• To drop a constraint on a view, you must drop the view and recreate it without the