Types of Views: Read-only and Update-able

From InterBase

Go Up to Creating Views (Data Definition Guide)


When you update a view, the changes are passed through to the underlying tables from which the view was created only if certain conditions are met. If a view meets these conditions, it is update-able. If it does not meet these conditions, it is read-only, meaning that writes to the view are not passed through to the underlying tables.

Note:
The terms update-able and read-only refer to how you access the data in the underlying tables, not to whether the view definition can be modified. To modify the view definition, you must drop the view and then recreate it.

A view is update-able if all of the following conditions are met:

  • It is a subset of a single table or another update-able view.
  • All base table columns excluded from the view definition allow NULL values.
  • The SELECT statement of the view does not contain sub-queries, a DISTINCT predicate, a HAVING clause, aggregate functions, joined tables, user-defined functions, or stored procedures.

If the view definition does not meet all of these conditions, it is considered read-only.

Note:
Read-only views can be updated by using a combination of user-defined referential constraints, triggers, and unique indexes. For information on how to update read-only views using triggers, see Working with Triggers.

View Privileges

The creator of the view must have the following privileges:

  • To create a read-only view, the creator needs SELECT privileges for any underlying tables.
  • To create an update-able view, the creator needs ALL privileges to the underlying tables.

For more information on SQL privileges, see Planning Security.

Examples of Views

The following statement creates an update-able view: 

CREATE VIEW EMP_MNGRS (FIRST, LAST, SALARY) AS
 SELECT FIRST_NAME, LAST_NAME, SALARY
 FROM EMPLOYEE
 WHERE JOB_CODE = 'Mngr';

The next statement uses a nested query to create a view, so the view is read-only: 

CREATE VIEW ALL_MNGRS AS
 SELECT FIRST_NAME, LAST_NAME, JOB_COUNTRY FROM EMPLOYEE
 WHERE JOB_COUNTRY IN
 (SELECT JOB_COUNTRY FROM JOB
 WHERE JOB_TITLE = 'manager');

The next statement creates a view that joins two tables, and so it is also read-only: 

CREATE VIEW PHONE_LIST AS
 SELECT EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO
 FROM EMPLOYEE, DEPARTMENT
 WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO.

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Types_of_Views:_Read-only_and_Update-able&oldid=30932"