Create View Using WITH CHECK OPTION In SQL Server

In this article I will demonstrate how to create view using with check option.
  • 12374

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.

Create View

To create view. run following code.

use person

GO

--Drop the view if it currently exists

IF OBJECT_ID('dbo.vendordata', 'V') IS NOT NULL

  DROP VIEW dbo.vendordata;

GO

--Create a view using the WITH CHECK option

CREATE VIEW vendordata

AS

select * from vendors where vendorid = 22

After executing above code, execute vendordata view.

view_sql.jpg

If the vendordata adds a new vendor aman and inserts it through view without the check option, the data will inserted successfully.

inserdata-inView.jpg

Create View Using the WITH CHECK Option

Now alter above view to include the with check option and execute below code.

ALTER VIEW vendordata

AS

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.

inserdata-inViewWithCheckOption.jpg
© 2020 DotNetHeaven. All rights reserved.