A “with check option” is designed for updatable views whereas a “check constraint” (coinstraint_type “V”) specifies valid values for an individual column:
CHECK (region_name in ‘north’,’south’,’east’,’west’)
The “with check option”, it is used to prevent changes to a view that would produce rows that are not included in a sub-query.
For example, here we create a view that will only allows updates to dept=’finance’:
CREATE OR REPLACE VIEW
SELECT name, salary FROM emp WHERE dept = ‘finance’
WITH CHECK OPTION;
This means only the record of employees in the finance department can be updated using this finance_dept view. At update time we see:
insert into (select * from emp where dept = ‘finance’ with check option)
(select * from
(select sal, empno from emp, dept where dept = ‘finance’)
with check option)
set sal = 100000 where ename = ‘KING’;
A violation produces:
ORA-01402: view WITH CHECK OPTION where-clause violation
With an ORA-01412, the SQL tried to INSERT or UPDATE a record in a view that contained a WITH CHECK OPTION. The resulting INSERT or UPDATE violates the WHERE clause of the view.