A view is a table that appears to contain rows of data. A view exists as a definition of a query that produces rows of data. These rows of data exist elsewhere in the database.

To create a view requires an SQL statement:

CREATE VIEW counselling AS
   SELECT student_id, counsellor_no, region 
   FROM student

• The above example uses a query specification for a single table. Joins can be used to base a view on more than one table.
• Views can also be specified from other views. All views have underlying base tables.
• Column names can be specified in the view or use existing table column names by default.
• The data type of a column in a view remains the same as for the base table.
• The values in each view column are as the values in the final table produced from the query specification.

Joining tables to form a view, specifying view column names, ordering the columns:

CREATE VIEW counselling2 (s_name, s_no, region, c_name, c_no) AS                         //names view columns
   SELECT s.name, student_id, counsellor_no, s.region, c.name, counsellor_no             //orders view columns
   FROM student s, staff c                                                                                         //joins tables
   WHERE counsellor_no = staff_no

CREATE VIEW statement format:

CREATE VIEW <view name> (<column list>) AS
   <query specification>

Creating column names is optional. By default they will inherit the name from the base tables. Exceptions to this are:

• A conflict where the same column name exists in joined tables.
• The creation of columns from value expressions where no column name has been specified from which there would be inheritance.

In the above situations column names must be specified in the CREATE VIEW statement.

Views can be used to access a database. Logical processing dictates that the query specification from the view definition is processed first. A query statement can then be used on the resulting view as input data.

Remember a view contains no data!

Why views exist

• Database administrators etc can create views from complex query specifications enabling users to retrieve data with simple queries. The user need no knowledge of joins etc.
• A user may have repeated needs for data that a view can satisfy in a simpler manner.
• Requests may need more than one query. Views can be used to minimse queries.

Removing views

DROP VIEW <view name> 

Care must be taken when dropping views. Other views may be dependent on them. SQL Anywhere provides no safeguard through RESTRICT or CASCADE. Therefore dependent views would become meaningless.

Updating views

A view contains no data. Therefore it cannot be updated. However it is possible to UPDATE a base table with an INSERT statement through a view. This in effect will update the view. Think about it!

INSERT INTO mod_counselling
   VALUES ('s24', '6451', '1')

Points to note:

• If an INSERT statement does not include a primary key, the statement will fail.
• If an INSERT statement does not include those columns specified as NOT NULL, the statement will fail.

For a view to be updatable there are several conditions that apply to the query specification. This is in order that a mapping to a base table can be established:

• SELECT can only include column names (not value expressions) and cannot include DISTINCT.
• FROM can only include one table.
• WHERE cannot include a self referencing subquery.
• No GROUPBY clause and no HAVING clause.
• No UNION.

Comments, suggestions, ideas to
Stuart Banner