Create View Using WITH CHECK OPTION In SQL Server
Posted in SQL | Views February 19, 2020
In this article I will demonstrate how to create view using with check option.
In this article I will demonstrate how to create view using with check option. The with check option is used to protect the data which cause the where clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved.
With check option is useful when we want to limit inserts and updates with the same restrictions applied to the where clause.
To create view. run following code.
--Drop the view if it currently exists
IF OBJECT_ID('dbo.vendordata', 'V') IS NOT NULL
DROP VIEW dbo.vendordata;
--Create a view using the WITH CHECK option
CREATE VIEW vendordata
select * from vendors where vendorid = 22
After executing above code, execute vendordata view.
If the vendordata adds a new vendor aman and inserts it through view without the check option, the data will inserted successfully.
Create View Using the WITH CHECK Option
Now alter above view to include the with check option and execute below code.
ALTER VIEW vendordata
SELECT * from vendors where vendorid = 22
WITH CHECK OPTION
If the vendordata adds a new vendor aman and inserts it through view with the check option, the data will not inserted since it will violate the CHECK OPTION constraint.