18.5. WITH CHECK OPTION Clause

If WITH CHECK OPTION is specified, the viewed table has to be updatable. This is actually a fast way to check how your particular SQL implementation handles updatable VIEWs. Try to create a version of the VIEW in question using the WITH CHECK OPTION, and see if your product will allow you to create it. The WITH CHECK OPTION is part of the SQL-89 standard, which was extended in Standard SQL by adding an optional <levels clause>. CASCADED is implicit if an explicit LEVEL clause is not given. Consider a VIEW defined as

CREATE VIEW V1
AS SELECT *
     FROM Foobar
    WHERE col1 = 'A';

and now UPDATE it with

UPDATE V1 SET col1 = 'B';

The UPDATE will take place without any trouble, but the rows that were previously seen now disappear ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.