Inserting Data Through Views

If you create a view using with check option, each row that is inserted through the view must meet the selection criteria of the view.

For example, the stores_cal view includes all rows of the stores table for which state has a value of “CA”:

create view stores_cal
as select * from stores
where state = "CA"
with check option

The with check option clause checks each insert statement against the view’s selection criteria. Rows for which state has a value other than “CA” are rejected.

If a view is created with check option, all views derived from the base view must satisfy the view’s selection criteria. Each new row inserted through a derived view must be visible through the base view.

Consider the view stores_cal30, which is derived from stores_cal. The new view includes information about stores in California with payment terms of “Net 30:”

create view stores_cal30
as select * from stores_cal
where payterms = "Net 30"

Because stores_cal was created with check option, all rows inserted or updated through stores_cal30 must be visible through stores_cal. Any row with a state value other than “CA” is rejected.

stores_cal30 does not have a with check option clause of its own. This means you can insert or update a row with a payterms value other than “Net 30” through stores_cal30. The following update statement would be successful, even though the row would no longer be visible through stores_cal30:

update stores_cal30
set payterms = "Net 60"
where stor_id = "7067"

insert statements are not allowed on join views created with check option.

If you insert or update a row through a join view, all affected columns must belong to the same base table.