Semantic Integrity Constraints for Updatable Views - Advanced SQL Engine - Teradata Database

Besides defining constraints on your base tables, you can specify additional constraints on a user-by-user basis through updatable views by specifying constraints in a WHERE clause and enforcing them by specifying a WITH CHECK OPTION clause. Because views inherit the constraints defined for their underlying base tables as well as those defined for any intermediate views, the constraints they inherit from their underlying relations are called derived constraints.

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.

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’);

     ...
     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.’

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.

Updatable View Inheritance

All base table integrity constraints are enforced without exception. Another way of stating this is to say that updatable views inherit the integrity constraints of their underlying base tables.

Because views can be nested, it is also true that updatable views inherit the integrity constraints of their underlying updatable views (when such relationships exist) as derived constraints in addition to inheriting the integrity constraints of their underlying base tables.

For example, suppose you have two nested views defined on the payroll table as follows.

When user Joe logs on, he is assigned the view joe_payroll_entry defined with the following WITH CHECK OPTION integrity constraint.

     WHERE dept_no = 1350
     WITH CHECK OPTION

Joe can only update values for department 1350.

View joe_payroll_entry is defined on top of view payroll_entry, which restricts the ability to update payroll for any employee who earns a base salary of 200,000 USD or greater. This constraint is specified by the following WHERE clause in the view definition:

     WHERE base_salary < 200000
     WITH CHECK OPTION

Because of this derived constraint, Joe can only update annual salaries less than 200,000 USD in department 1350.

View payroll_entry is defined on top of base table payroll, which restricts the ability to update payroll to only those employees who are US citizens or who have valid work visas. This constraint is specified by the following column-level CHECK constraint in the table definition:

     visa_code CHARACTER(2)
       CONSTRAINT ok2work CHECK (visa_code IN (‘US’,’WV’)

Because of this additional derived constraint, Joe can only update salaries of US citizens or individuals with valid work visas who work in department 1350 and earn annual salaries less than 200,000 USD.