WITH CHECK OPTION Keywords

The WITH CHECK OPTION keywords instruct the database server to ensure that all modifications that are made through the view to the underlying tables satisfy the definition of the view.

The following example creates a view that is named palo_alto, which uses all the information in the customer table for customers in the city of Palo Alto. The database server checks any modifications made to the customer table through palo_alto because the WITH CHECK OPTION keywords are specified.

CREATE VIEW palo_alto AS
   SELECT * FROM customer WHERE city = 'Palo Alto'
      WITH CHECK OPTION

You can insert into a view a row that does not satisfy the conditions of the view (that is, a row that is not visible through the view). You can also update a row of a view so that it no longer satisfies the conditions of the view. For example, if the view was created without the WITH CHECK OPTION keywords, you could insert a row through the view where the city is Los Altos, or you could update a row through the view by changing the city from Palo Alto to Los Altos.

To prevent such inserts and updates, you can add the WITH CHECK OPTION keywords when you create the view. These keywords ask the database server to test every inserted or updated row to ensure that it meets the conditions that are set by the WHERE clause of the view. The database server rejects the operation with an error if the row does not meet the conditions.

Even if the view was created with the WITH CHECK OPTION keywords, however, you can perform inserts and updates through the view to change columns that are not part of the view definition. A column is not part of the view definition if it does not appear in the WHERE clause of the SELECT statement that defines the view.

The CREATE VIEW statement fails with error -940 if you include the WITH CHECK OPTION keywords in a CREATE VIEW statement in which the UNION, INTERSECT, MINUS, or EXCEPT set operator combines two queries in the view definition.