SQL Server Views WITH CHECK OPTION

Views can be created in SQL Server WITH CHECK OPTION.
WITH CHECK OPTION will make sure that all INSERT and UPDATE statements executed against the view meet the restrictions in the WHERE clause, and that the modified data in the view remains visible after INSERT and UPDATE statements.

To test this, we will first create a table and insert some data in it:

CREATE TABLE table_1
(
id int,
data nvarchar(20)
)

INSERT INTO table_1
VALUES (1, 'a'), (2, 'b'), (3, 'c')

Next we will create a view using the WITH CHECK option:

CREATE VIEW view_1
AS
SELECT * FROM table_1
WHERE data like 'b%'
WITH CHECK OPTION

We can see only values in the data column that start with b:

SELECT * FROM view_1

view_with_check_select_1

If we try to insert into view value that doesn't meet the WHERE criteria (WHERE data like 'b%'), it will fail:

INSERT INTO view_1
VALUES (4, 'd')

view_with_check_insert_1

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.

We can only insert values that meet the WHERE criteria:

INSERT INTO view_1
VALUES (4, 'bbb')

view_with_check_insert_2

The same error happens if we try to update a value, and the result of the UPDATE statement would make the value not visible through the view:

UPDATE view_1
SET data = 'd'
WHERE id = 2

view_with_check_update_1

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.

If we update a value, and the result of the UPDATE statement meets the WHERE clause of the view (WHERE data like 'b%'), it will succeed:

UPDATE view_1
SET data = 'bbbbb'
WHERE id = 2

view_with_check_update_2

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">