15.10 - WITH CHECK OPTION - Teradata Database

Restricts the rows that can be updated in the table by an INSERT or UPDATE request.

The following rules apply to updatable views and WITH CHECK OPTION.

• If WITH CHECK OPTION is specified, the view should be updatable. If a view is not updatable, then specifying WITH CHECK OPTION has no effect.

  In addition, any INSERT or UPDATE operations on an underlying base table through the view does not create a row for which the WHERE clause condition evaluates to FALSE.

• If WITH CHECK OPTION is not specified in an updatable view, then any WHERE clause contained in the query defining the view is ignored for any INSERT or UPDATE request made through the view.
• If you create nested views, you can define them to reference only a single base table, which might allow those views to be updatable. In this case, the presence of a WITH CHECK OPTION in a view definition causes the WHERE clause for that view, and WHERE clauses of any underlying views, to be checked in the constraint for INSERT or UPDATE requests.

Example: Creating a View for Names and Job Titles Only

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, jobtitle 
     FROM employee 
     WHERE DeptNo = 300 
    WITH CHECK OPTION;

The WITH CHECK OPTION prevents using this view to INSERT a row into employee through the view, or to update any row of employee for which DeptNo <> 300.