Views 'with check' Option

AjeetPrasad

 Profile Answers by AjeetPrasad 

• Oct 8th, 2008

 

This restriction determines what types of modifications you can make through views with check options. If a view has a with check option clause, each row inserted or updated through the view must be visible within the view.

dhampack

 Profile Answers by dhampack 

• Nov 23rd, 2008

 

When you create a view using the with check option clause, each insert and update through the view, is validated against the view’s selection criteria. If the rows inserted or updated are out of criteria then it fails and gives error message. For ex, you have a view named sybase_books
create view sybase_books as select * from books where type='sybase' with check option.
If you try to insert rows through view where type='oracle' then it will fail, for ex
insert into sybase_books values ('Oracle theory','oracle')

Tejas More

 Profile Answers by Tejas More 

• Jul 7th, 2011

 

The with check option flag prevents insertion or updating of rows that will not meet the view criteria.

e.g.:
create view cal_publishers_chk as

select *

from publishers

where state = “CA”

with check option



update cal_publishers_chk

set state = “OH”


Update fails because, after the update, the modified rows would fail to appear in the view.