WITH CHECK OPTION

WITH CHECK OPTION
Rows exist in a view because they satisfy the WHERE condition of the defining query. If
a row is altered such that it no longer satisfies this condition, then it will disappear from
the view. Similarly, new rows will appear within the view when an insert or update on the
view cause them to satisfy the WHERE condition. The rows that enter or leave a view are
called migrating rows.
Generally, the WITH CHECK OPTION clause of the CREATE VIEW statement
prohibits a row migrating out of the view. The optional qualifiers LOCAL/CASCADED
are applicable to view hierarchies: that is, a view that is derived from another view. In
this case, if WITH LOCAL CHECK OPTION is specified, then any row insert or update
on this view, and on any view directly or indirectly defined on this view, must not cause
the row to disappear from the view, unless the row also disappears from the underlying
derived view/table. If the WITH CASCADED CHECK OPTION is specified (the default
setting), then any row insert or update on this view and on any view directly or indirectly
defined on this view must not cause the row to disappear from the view.
This feature is so useful that it can make working with views more attractive than
working with the base tables. When an INSERT or UPDATE statement on the view
violates the WHERE condition of the defining query, the operation is rejected. This
enforces constraints on the database and helps preserve database integrity. The WITH
CHECK OPTION can be specified only for an updatable view, as defined in the previous
Example 6.6 WITH CHECK OPTION
Consider again the view created in Example 6.3:
CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branchNo = ‘B003’
WITH CHECK OPTION;
with the virtual table shown in Table 6.3. If we now attempt to update the branch number
of one of the rows from B003 to B005, for example:
UPDATE Manager3Staff
SET branchNo = ‘B005’
WHERE staffNo = ‘SG37’;
then the specification of the WITH CHECK OPTION clause in the definition of the view
prevents this from happening, as this would cause the row to migrate from this horizontal
view. Similarly, if we attempt to insert the following row through the view:
INSERT INTO Manager3Staff
VALUES(‘SL15’, ‘Mary’, ‘Black’, ‘Assistant’, ‘F’, DATE‘1967-06-21’, 8000, ‘B002’);
then the specification of WITH CHECK OPTION would prevent the row from being
inserted into the underlying Staff table and immediately disappearing from this view (as
branch B002 is not part of the view).
Now consider the situation where Manager3Staff is defined not on Staff directly but on
another view of Staff:
CREATE VIEW LowSalary CREATE VIEW HighSalary CREATE VIEW Manager3Staff
AS SELECT * AS SELECT * AS SELECT *
FROM Staff FROM LowSalary FROM HighSalary
WHERE salary > 9000; WHERE salary > 10000 WHERE branchNo = ‘B003’;
WITH LOCAL CHECK OPTION;
If we now attempt the following update on Manager3Staff:
UPDATE Manager3Staff
SET salary = 9500
WHERE staffNo = ‘SG37’;
then this update would fail: although the update would cause the row to disappear from the
view HighSalary, the row would not disappear from the table LowSalary that HighSalary is
derived from. However, if instead the update tried to set the salary to 8000, then the update
would succeed as the row would no longer be part of LowSalary. Alternatively, if the view
HighSalary had specified WITH CASCADED CHECK OPTION, then setting the salary to
either 9500 or 8000 would be rejected because the row would disappear from HighSalary.
Therefore, to ensure that anomalies like this do not arise, each view should normally be
created using the WITH CASCADED CHECK OPTION.