The CHECK OPTION ensures that the data that is used to create a view cannot be changed to create invalid data in the underlying base tables of the view. This document will walk through an overview of the CREATE VIEW statement and review how to use the CHECK OPTION.
Overview of The CREATE VIEW Statement
The CREATE VIEW statement (Transact-SQL) is a statement used to define a new view. A view is created by selecting fields from one or more tables in the database. A view acts as a virtual table, by executing a SELECT statement the underlying tables are queried and the results are ‘saved’ in the view. By using a view a user can select, insert, update and delete data in the underlying tables.
The general syntax for the CREATE VIEW statement is as follows:
CREATE VIEW [ SCHEMA_NAME . ] VIEW_NAME [ (Column_Alias [ ,..n ] ) ] as < SELECT_Statement > [ WITH CHECK OPTION ]
Using the CHECK OPTION
The CHECK OPTION ensures that the data that is used to create a view cannot be changed to create invalid data in the underlying base tables of the view. The CHECK OPTION ensures that the view's fields must be valid in the base tables, it can prevent changes when a user updates or inserts data through a view.
The general syntax for using the CHECK OPTION with the CREATE VIEW statement is as follows:
CREATE VIEW [SCHEMA_NAME.]VIEW_NAME [ (Column_Alias[,...n])] as
WITH CHECK OPTION
The CHECK OPTION statement can be used as part of the CREATE OR REPLACE VIEW statement, its syntax is as follows:
CREATE OR REPLACE VIEW [SCHEMA_NAME.]VIEW_NAME[ (Column_Alias[,...n])] AS
WITH CHECK OPTION
FAQ
Q: Where can I find additional information on the CHECK OPTION?
A: You can find additional information on the CHECK OPTION on Microsoft’s documentation.
Q: Can I use the CHECK OPTION with the CREATE OR REPLACE VIEW statement?
A: Yes, the CHECK OPTION statement can also be used as part of the CREATE OR REPLACE VIEW statement.
Q: Is the CHECK OPTION mandatory when creating a view?
A: No, the CHECK OPTION is not mandatory. However, it is generally advised to include it with the view statement in order to guarantee the data integrity of the view.
Q: What does the CHECK OPTION do?
A: The CHECK OPTION ensures that the data that is used to create a view cannot be changed to create invalid data in the underlying base tables of the view.
Q: What happens when a user tries to update or insert data through a view with the CHECK OPTION enabled?
A: If a user tries to update or insert data through a view with the CHECK OPTION enabled and the data is invalid, the process will be aborted. This prevents the underlying base tables of the view from being updated with invalid data.