17.10 - WITH CHECK OPTION Clause in Views - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

WITH CHECK OPTION pertains 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 of a DELETE, INSERT, MERGE, SELECT, or UPDATE request, 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 request 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. Base table constraints are not affected by this circumvention and continue to be enforced.

Unless you have compelling reasons not to honor the WHERE clause conditions specified by a view definition, you should always specify a WITH CHECK OPTION clause in all your updatable view definitions to protect the integrity of your databases (see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100 for more information about various aspects of maintaining system integrity).

When WITH CHECK OPTION is … 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.

For example, a query could specify WHERE column_1 > 10, which restricts the rows that can be seen through the view having a value for column_1 greater than 10, while the base table could have values less than or equal to 10 as long as no constraint in the table definition prevented values in that range from being inserted.