Creating Views

---

A view is a logical subset of one or more base tables or views where columns are chosen by naming them and rows are chosen through specified conditions relating to column values.

Views are created, for example, so that users who need not see all the data in a single table are shown only the parts of the table that interest them (restriction views). Views can also be created as a combination of a number of columns from several different tables (join views).

Operations on views are actually performed on the underlying base tables. Certain view definitions do not allow data to be changed in the view (read-only views). See Updatable Views for further details.

View names can be up to 128 characters long. Views are defined in terms of a SELECT statement; the result of the SELECT statement forms the contents of the view. There are no restrictions on which select statements that can be used in a view definition.

Creating a View

Create a restriction view on the CUSTOMERS table called CUSTOMER_DETAILS containing limited information:

 CREATE VIEW customer_details
    AS SELECT surname, forename, address_1, address_2, town, postcode,
              title, date_of_birth, country_code, customer_id
    FROM customers;
 

The columns in the view are named after the columns listed in the SELECT clause, since the view definition does not include a list of column names.

Create a join view, including an outer join:

 CREATE VIEW product_details
    AS SELECT product, COALESCE(producer, ' ') AS producer, format,
              price, stock, reorder_level, release_date, ean_code,
              status, product_search, item_id, category_id, product_id,
              display_order, image_id
    FROM products
    NATURAL JOIN items
    NATURAL JOIN formats
    NATURAL LEFT OUTER JOIN producers;

Check Option

The check option can be used in updatable view definitions to limit the data that can be inserted into the view. If a check option is specified, data which does not fulfill the definition of the view cannot be inserted into the view.

 CREATE VIEW swedish_customers
    AS SELECT *
    FROM customer_details
    WHERE country_code = 'SE'
    WITH CHECK OPTION;
 

The check option in the view definition (WITH CHECK OPTION) means that the COUNTRY_CODE column must be set to SE if new rows are inserted to into the view or rows are updated using the view.

If there is an instead of trigger defined for the view, the WITH CHECK OPTION does not have any effect.

Creating Views Based on Other Views

Views can be based on other views. When a view is created based upon another view or views, the original view's limitations are carried over to the new view.

 CREATE VIEW customer_addresses (surname, forename, recipient,
       address_1, address_2, town, postcode, country,
       salutation, customer_id)
    AS SELECT surname, forename,
       UPPER(recipient(title, forename, surname)), address_1,
       COALESCE(address_2, ' '), UPPER(town), UPPER(postcode), UPPER(country),
       salutation(title, forename, surname, date_of_birth, country_code),
       customer_id
    FROM customer_details
    JOIN countries ON code = country_code;

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se