WITH CHECK OPTION on a view

 

WITH CHECK OPTION is an optional clause on the CREATE VIEW statement. It specifies the level of checking when data is inserted or updated through a view.

If WITH CHECK OPTION is specified, every row that is inserted or updated through the view must conform to the definition of the view. The option cannot be specified if the view is read-only. The definition of the view must not include a subquery.

If the view is created without a WITH CHECK OPTION clause, insert and update operations that are performed on the view are not checked for conformance to the view definition. Some checking might still occur if the view is directly or indirectly dependent on another view that includes WITH CHECK OPTION. Because the definition of the view is not used, rows that do not conform to the definition of the view might be inserted or updated through the view. This means that the rows cannot be selected again through the view.

• WITH CASCADED CHECK OPTION
  The WITH CASCADED CHECK OPTION clause specifies that every row that is inserted or updated through a view must conform to the definition of the view.

• WITH LOCAL CHECK OPTION
  The WITH LOCAL CHECK OPTION clause is identical to the WITH CASCADED CHECK OPTION clause except that you can update a row so that it can no longer be retrieved through the view. This can happen only when the view is directly or indirectly dependent on a view that was defined with no WITH CHECK OPTION clause.

• Example: Cascaded check option
  This example shows how the check option is enforced on a number of dependent views that are defined with or without a check option.

 

Parent topic:

Creating and using views

 

Related reference

CREATE VIEW