Oracle "with check option" syntax Don Burleson

A "with check option" is designed for updatable views whereas a "check constraint" (coinstraint_type "V") specifies valid values for an individual column:

region_name       varchar(5),      

      CONSTRAINT check_region

      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

   finance_dept

AS

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)

    values (...);

update

   (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.

Related links:

Oracle Update Statement