WITH CHECK OPTION in View

I have already discussed about Views in my previous post. In this article we will know the use of WITH CHECK OPTION on Views.

When we create a view without CHECK Option, in that case we can perform INSERT, UPDATE, DELETE operation on View. If records are not in scope of View and we want to perform DELETE and UPDATE operation on those records then DELETE and UPDATE operation will be succeed without any modification in the base table but INSERT operation will succeed and it will insert new records in base table.

When we create a view WITH CHECK OPTION, in that case we can’t perform INSERT, UPDATE, DELETE operation on those records which are not satisfying the criteria set within the select statement of View. If we try to perform DELETE and UPDATE operation on out of scope records then the operation will be succeed without any modification in the base table.

Let’s go through a simple example

USE TEACHMESQLSERVER

GO

-- Creating a new table and popluating data into the table

IF OBJECT_ID('EMP','U') IS NOT NULL

DROP TABLE EMP

GO

CREATE TABLE EMP( EMPID INT, EMPNAME VARCHAR(MAX))

GO

INSERT INTO EMP VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')

GO

-- Creating a View without CHECK Option

IF OBJECT_ID('EMPVIEW','V') IS NOT NULL

DROP VIEW EMPVIEW

GO

CREATE VIEW EMPVIEW

AS

SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5

GO

SELECT * FROM EMPVIEW

--Performing DML Operation on VIEW

GO

INSERT INTO EMPVIEW VALUES(6,'F') -- Successfully Inserted new record in Base Table

GO

SELECT * FROM EMP

GO

UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO

DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO

SELECT * FROM EMP

WITH CHECK OPTION EXAMPLE

-- ALTERING the View WITH CHECK OPTION

GO

ALTER VIEW EMPVIEW

AS

SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5

WITH CHECK OPTION

--Performing DML Operation on VIEW

GO

UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO

DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO

INSERT INTO EMPVIEW VALUES(7,'H') -- Failed with below error, because the INSERT statement is not satisfying the Cretria set in Select Statement, VIEW can have all records where EMPID<5 in this case EMPID is greater than 5 so it failed.

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

GO

INSERT INTO EMPVIEW VALUES(0,'I') -- Successfully Inserted new record in Base Table because it satisfied EMPVIEW Criteria.

GO

SELECT * FROM EMP