Database Reference
In-Depth Information
of the rows or a subset of the columns. The best explanation for this
type of view is security where, for instance, different customers shar-
ing the same database can only view their own data. With a few
restrictions (examined later in this chapter), you can update the table
on which the view is built by updating the view. You can also insert
and delete rows in the base table through the view.
Constraint View
. A constraint view can be used to insert a new row
into the underlying table as long as the row would be returned by the
query, or the row exists for the view. For example, if the view only
looks at ARTIST rows in the USA, you could not insert an ARTIST
row where the artist is in France. The same rule applies to rows that
are updated via the constraint view. Most constraint views are based
on simple views, although certain complex views can also be used as
constraint views. Constraint views are most often used as an easy way
to enforce business rules in applications without the application
developer doing any extra coding.
Note:
This approach applies views to ease of application coding rather than
security. Views are possibly more applicable in client-server environments.
Scalability issues may arise for large, very busy OLTP databases.
Complex View
. A complex view contains a query on more than one
table. This type of view allows you to wrap complexities inside the
view's query so they are hidden from the user or application devel-
oper. Complex views are most often used for simplifying end-user
reporting by providing a table-like structure for users to query. For
example, you could create a view that displays the CD title, artist
name, and song title (which are found in three different tables).
Complex views usually cannot be used to insert, update, or delete
rows from the underlying tables.
19.3
CREATE VIEW Syntax
Figure 19.1 shows the syntax of the CREATE VIEW statement. The same
syntax applies to all types of views.
The next three sections look at how to create each of the three types of
views: simple, constraint, and complex.
