17.10 - Semantic Constraint Enforcement - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

The enforcement of a constraint depends on how the base table on which it is defined is accessed. If the base table is accessed directly, then its column and table constraints are always enforced. Date (2001a) calls this “The Golden Rule,” which he defines as follows: No update operation must ever assign to any database a value that causes its database predicate to evaluate to false. This, of course, is a generalization of the Closed World Assumption (see The Closed World Assumption and The Closed World Assumption Revisited).

By this definition, the checking time must always be immediate for any update; that is, the constraint check is made at statement boundaries, not deferred for checking at transaction (COMMIT time) boundaries. If this were not the case, then inconsistent, or false, data could be entered into the database, even if only for a brief time and even if the inconsistency were, as it must be, private to the transaction in question. It would still be possible for a query that followed this inconsistent update within the boundaries of an explicit transaction to report erroneous information. Vantage does not support deferred integrity checking.

If a base table is accessed by means of a view, then the enforcement of any WHERE clause constraints specified in the view definition depends on whether the view is also defined WITH CHECK OPTION or not (see Semantic Integrity Constraints for Updatable Views).

Views inherit the constraints of their underlying base tables; therefore, base table constraints cannot be violated by updating through a view. However, additional constraints defined by means of a WHERE clause specification in a view definition can be bypassed if the view is not also defined WITH CHECK OPTION. The result is not a violation of any constraints defined on underlying base tables, but the insertion of a row that cannot be seen from that view.