Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Working with Database Objects » Working with views

Using views Next Page

Using the WITH CHECK OPTION clause

The WITH CHECK OPTION clause is useful for controlling what data is changed when inserting into, or updating, a base table through a view. The following example illustrates this.

Execute the following statement to create the SalesEmployees view with a WITH CHECK OPTION clause.

CREATE VIEW SalesEmployees AS
   SELECT EmployeeID, GivenName, Surname, DepartmentID
   FROM Employees
   WHERE DepartmentID = 200

Select to view the contents of this view, as follows:

SELECT * FROM SalesEmployees;
EmployeeID GivenName Surname DepartmentID
129 Philip Chin 200
195 Marc Dill 200
299 Rollin Overbey 200
467 James Klobucher 200
... ... ... ...

Next, attempt to update DepartmentID to 400 for Philip Chin:

UPDATE SalesEmployees
SET DepartmentID = 400 
WHERE EmployeeID = 129;

Since the WITH CHECK OPTION was specified, the database server evaluates whether the update violates anything in the view definition (in this case, the expression in the WHERE clause). The statement fails (DepartmentID must be 200), and the database server returns the error, "WITH CHECK OPTION violated for insert/update on base table 'Employees'."

If you had not specified the WITH CHECK OPTION in the view definition, the update operation would proceed, causing the Employees table to be modified with the new value, and subsequently causing Philip Chin to disappear from the view.

The check option is inherited

If a view (for example, View2) is created that references the SalesEmployees view, any updates or inserts on View2 are rejected that would cause the WITH CHECK OPTION criteria on SalesEmployees to fail, even if View2 is defined without a WITH CHECK OPTION clause.