Defining and Dropping View Constraints

In contrast to table constraints, unique and referential constraints cannot be defined on a view. A type of check constraint is available with the CREATE VIEW statement. The WITH CHECK OPTION ensures that modifications made through an updatable view satisfy all conditions of the view definition.

The following example defines a view check constraint named DateCheck based on the view definition of the updatable view named RecDB.EventView:

        CREATE VIEW RecDB.EventView

                    (Event,

                     Date)

                 AS

             SELECT RecDB.Event,

                    RecDB.Date

               FROM RecDB.Events

              WHERE Date >= CURRENT_DATE

  WITH CHECK OPTION CONSTRAINT DateCheck

When modifications are made through a view defined WITH CHECK OPTION, the new values must be visible in the view definition. That is, any attempt to change data through such a view must satisfy all conditions in the query specification. If this is not so, the view check is violated, an error is returned, and the statement has no effect.

Note that view check constraints are not deferrable, and a SET CHECK DEFERRED statement does not affect them.

Also note that to drop a view check constraint, you must drop the view and recreate it.