15.00 - WITH CHECK OPTION Clause in Views - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

WITH CHECK OPTION Clause in Views

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 Database Design and Security Administration 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.