15.00 - Updatable View Inheritance - Teradata Database

Teradata Database Design

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

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.