What does CHECK OPTION do in a View

When CHECK OPTION specified in a view definition, all the data modification and insertion through the view will be forced to qualify the view’s filter criteria.

I will use the “Countries” table from the “WideWorldImporters” database for this demonstration. This table has country information such as the name of the country and its continent and the population etc.,
The following view is created to provide all the countries from Oceania region.

In the view definition the keyword “with check option” is included to enforce the update or insert operations to qualify filter criteria of the view. In other words, the statement will be executed only if the modified data is visible through the view after the execution.

create view dbo.vw_countries_oceania
as
select * from application.countries  
where continent='Oceania'
with check option

The continent of Australia is stored as Oceania in the table, let us try to update the continent as Australia for the country Australia using the view.

update dbo.vw_countries_oceania 
	set Continent='Australia' 
where Countryname ='Australia'

This statement will fail because when the continent name is changed from Oceania to Australia the view will no longer able to return this record so the update statement is treated as a non-qualified query.

What if the same update statement is executed on the view but now without the check option.

alter view dbo.vw_countries_oceania
as
	select * from application.countries  
	where continent='Oceania'
go
update dbo.vw_countries_oceania 
	set continent='Australia' 
where countryname ='Australia'

The update is successful because now it is not enforcing the data modification to meet the filter condition of the view.