Updating Views: WITH CHECK OPTION






Updating Views: WITH CHECK OPTION

We have already shown a number of examples of views being updated. In fact, the underlying tables are being updated. Nevertheless, updating views can have unexpected results. Let us illustrate this with the following example:

9. Create a view that holds all players born before 1960.

CREATE   VIEW VETERANS AS
SELECT   *
FROM     PLAYERS
WHERE    BIRTH_DATE < '1960-01-01'

Now we would like to change the date of birth of the veteran whose player number is 2 from 1 September 1948 to 1 September 1970. The update statement reads:

UPDATE   VETERANS
SET      BIRTH_DATE = '1970-09-01'
WHERE    PLAYERNO = 2

This is a correct update. The date of birth of player number 2 in the PLAYERS table is changed. The unexpected effect of this update, though, is that if we look at the view using a SELECT statement, player 2 no longer appears. This is because when the update occurred, the player ceased to satisfy the condition specified in the view formula.

If you extend the view definition using the so-called WITH CHECK OPTION, SQL ensures that such an unexpected effect does not arise.

The view definition then becomes:

CREATE   VIEW VETERANS AS
SELECT   *
FROM     PLAYERS
WHERE    BIRTH_DATE < '1960-01-01'
WITH     CHECK OPTION

If a view includes the WITH CHECK OPTION clause, all changes with UPDATE, INSERT, and DELETE statements are checked for validity:

  • An UPDATE statement is correct if the rows that are updated still belong to the (virtual) contents of the view after the update.

  • An INSERT statement is correct if the new rows belong to the (virtual) contents of the view.

  • A DELETE statement is correct if the rows that are deleted belong to the (virtual) contents of the view.

As said, a view can be stacked on top of another view. The question that comes to mind then is to what extent the check of the WITH CHECK OPTION can be carried out. If we specify WITH CASCADED CHECK OPTION, all views are checked. When WITH LOCAL CHECK OPTION is used, only those checks are carried out that relate to conditions that appear in the view that will be updated. CASCADED is the default.

10. Create a view of all players born before 1960 and living in Inglewood.

CREATE   VIEW INGLEWOOD_VETERANS AS
SELECT   *
FROM     VETERANS
WHERE    TOWN = 'Inglewood'
WITH     CASCADED CHECK OPTION

Explanation: If we use an INSERT statement to add a player to this view, he or she must live in Inglewood and must be born before January 1, 1960. When we leave out CASCADED, every player who we add to the INGLEWOOD_VETERANS table must live in Inglewood. SQL no longer carries out the check.

The WITH CHECK OPTION can be used only in conjunction with views that can be updated according to the rules mentioned in Section 21.8.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows