WITH CHECK OPTION Clause
The WITH CHECK OPTION clause prevents you from executing an insert or update to a view that creates a row that does not comply with the view definition (the qualification specified in the WHERE clause).
For example, if the following view is defined WITH CHECK OPTION:
CREATE VIEW myview
AS SELECT *
FROM mytable
WHERE mycolumn = 10
WITH CHECK OPTION;
And the following update is attempted:
UPDATE myview SET mycolumn = 5;
The update to the mycolumn column is rolled back, because the updated rows fail the mycolumn = 10 qualification specified in the view definition. If the WITH CHECK OPTION is omitted, any row in the view can be updated, even if the update results in a row that is no longer a part of the view.
The WITH CHECK OPTION is valid only for updatable views. The WITH CHECK OPTION clause cannot be specified if the underlying base table is used in a subselect in the SELECT statement that defines the view. You cannot update or insert into a view defined on top of a view specified with WITH CHECK OPTION if the resulting rows violate the qualification of the underlying view.
Last modified date: 04/26/2024