ALLBASE/SQL Reference Manual (36216-90216)

68 Chapter2
Using ALLBASE/SQL
Defining How Data is Stored and Retrieved
4. Specification of WITH CHECK OPTION, if desired
The following example contains numbers that refer to the view components listed above:
1
|
CREATE VIEW HiPrice
(PartNum, Price) --2
AS SELECT PartNumber, SalesPrice ---
FROM PurchDB.Parts |--3
WHERE SalesPrice > 1000 ---
View names are governed by the same rules as table names.
The columns in a view can have the same names as the columns in the table(s) they are
based on, or they can have different names. You only need to include column names in a
view definition if you are using multiple base tables which have duplicate column names or
if you want to rename the columns. You enclose the names in parentheses, but omit data
types, which depend on the types of the columns in the base tables.
The derivation of the view is a SELECT statement. In the previous example, the view is
derived from the PurchDB.Parts table. Each row in the view contains a part number and a
price; only rows for parts costing more than $1000 can be accessed through this view.
Unlike a table definition, a view definition does not require that you specify where to store
rows. A view is a SELECT statement stored in the system catalog, not a physical copy of the
data; ALLBASE/SQL extracts data from physical tables at the time you use the view.
Views can be used for both retrieving and modifying data. Refer to “Updatability of
Queries” in Chapter 3 , “SQL Queries,” for restrictions governing the use of a view to
change data in a base table.
The WITH CHECK OPTION for views is described in Chapter 4 , “Constraints, Procedures,
and Rules.”
Creating Indexes
You can create an index on one or more columns in a query. An index can provide quick
access to the data in your tables. For information on indexes, refer to section
“Understanding Data Access Paths” later in this section.
Specifying Integrity Constraints
Using integrity constraints helps to ensure that a database contains only valid data.
Integrity constraints provide a way to check data within the database system rather than
by coding elaborate validation checks within application programs. An integrity constraint
is either a unique constraint, a referential constraint, or a check constraint. These
constraints are described in Chapter 4 , “Constraints, Procedures, and Rules.”
Creating Procedures
You can define procedures to enforce relationships among database tables or to automate
nearly any operation in the DBEnvironment. The following example shows creating a
procedure to perform deletions from the SupplyPrice table in the sample DBEnvironment