15.00 - Specifying Integrity Constraints in an Updatable View Definition - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Specifying Integrity Constraints in an Updatable View Definition

Not all views are updatable. You can tailor update constraints at the user level by specifying user-specific constraints in the WHERE clause of a view as long as you also specify a WITH CHECK OPTION clause.

WITH CHECK OPTION pertains only to updatable views. Views are typically created to restrict which base table columns and rows a user can access in a table. Base table column projection is specified by the columns named in the column_name list, while base table row restriction is specified by an optional WHERE clause.

The WITH CHECK OPTION clause is an integrity constraint option that restricts the rows in the table that can be affected by an INSERT or UPDATE statement to those defined by the WHERE clause. If you do not specify WITH CHECK OPTION, then the integrity constraints specified by the WHERE clause are not checked for updates. Derived constraints inherited from underlying relations are not affected by this circumvention and continue to be enforced. The problem is that the view that updated the row in a way that violates the WHERE clause cannot view the updated row, so it cannot be updated in the future through that view.

 

WHEN WITH CHECK OPTION is …

THEN any insert or update made to the table through the view …

specified

only inserts or updates rows that satisfy the WHERE clause.

not specified

ignores the WHERE clause used in defining the view.

The following rules apply to updatable views and the WITH CHECK OPTION.

  • If WITH CHECK OPTION is specified, the view is updatable. Any insert or update to the table through the view is rejected if the WHERE clause predicate evaluates to false.
  • If WITH CHECK OPTION is not specified in an updatable view, then any WHERE clause contained in the view definition is ignored for any insert or update action performed through the view. In other words, the specified integrity constraints in the view definition are ignored, so you should always specify a WITH CHECK OPTION clause to define constrained updatable views unless you have an extraordinary reason not to.
  • You can define nested views that only reference a single base table, which might allow the views to be updatable. In this case, the specification of a WITH CHECK OPTION clause in the view definition permits the WHERE clause constraints for that view, as well as those defined for any underlying views, to be exercised in the constraint on INSERT or UPDATE. See “Updatable View Inheritance” on page 662 for more information.
  • The following request creates a view of the employee table so that it provides access only to the names and job titles of the employees in department 300:

         CREATE VIEW dept300 (Name, JobTitle) AS 
          SELECT name, job_title 
          FROM employee 
          WHERE dept_no = 300 
          WITH CHECK OPTION;

    The WITH CHECK OPTION clause in this example prevents you from using the Dept300 view to update a row in the Employee table for which DeptNo <> 300.

    This example shows how using a view in an UPDATE, INSERT, or DELETE statement allows you to add, change, or remove data in the base table set on which the view is defined.

    Consider the following staff_info view, which provides a personnel clerk with retrieval access to employee numbers, names, job titles, department numbers, sex, and dates of birth for all employees except vice presidents and managers:

         CREATE VIEW staff_info (number, name, position,
           department, sex, dob) AS
          SELECT employee.empno, name, jobtitle, deptno, sex, dob 
          FROM employee
          WHERE jobtitle NOT IN (’Vice Pres’, ’Manager’)
          WITH CHECK OPTION ;

    If the owner of staff_info has the insert privilege on the employee table, and if the clerk has the insert privilege on staff_info, then the clerk can use this view to add new rows to employee.

    For example, this request inserts a row into the underlying employee table that contains the specified information:

         INSERT INTO staff_info (number,name,position,department,sex,dob) 
          VALUES (10024, ’Crowell N’, ’Secretary’, 200, ’F’, ’Jun 03
          1960’);

    Note: The constraint on staff_info illustrated by the following WHERE clause applies to any insert using this view that includes the WITH CHECK OPTION phrase.

         ...
         WHERE jobtitle NOT IN (’Vice Pres’, ’Manager’) 
         ...

    Therefore, the preceding INSERT statement would fail if the position entered for employee Crowell was ‘Vice Pres’ or ‘Manager.’

    Note: If this view were defined without the WITH CHECK OPTION, and a user had the UPDATE privilege on the table, that user could update a job title to ‘Vice Pres’ or ‘Manager’. The user would be unable to access the changed row through the view.

    The following statement changes the department number typed for Crowell in the preceding INSERT request from 200 to 300:

         UPDATE staff_info 
          SET department = 300 
          WHERE number = 10024;

    Performing the following DELETE request removes the row for employee Crowell from the staff_info table.

         DELETE 
          FROM staff_info 
          WHERE number = 10024;

    Views are a useful method for permitting selected users to have restricted access to base table data. However, as the preceding examples suggest, granting another user insert, update, and delete privileges on a view means relinquishing some control over your data. Carefully consider granting such privileges.

    The default is not to constrain updated or inserted values unless the view definition explicitly includes WITH CHECK OPTION.