WITH CHECK OPTION Clause in VIEW

Using the WITH CHECK OPTION Clause.

A modifiable join view is a view that contains more than one table in the top level from clause of the SELECT statement, and that does not contain any following.

• Distinct Operator
• Aggregate Functions : AVG,COUNT,GLB,MAX,MIN,STDDEV,SUM or VARIANCE
• Set Operators :  UNION , UNION ALL , MINUS ,INTERSECT .
• GROUP BY or HAVING Clauses.
• START WITH or CONNECT BY clauses
• PSEUDO Columns : ROWNUM ,ROWID

Any UPDAE ,INSERT , or DELETE statement on a join view can modify only one underlying base table.

The WITH CHECK OPTION Clause  specifies the level of checking to be done when doing DML against the view . if specified every row that is inserted , updated or deleted through the view must confirm to the definition of the view.

Specify WITH READ ONLY to indicates that the table or view can not be updated.

You can ensure that DML on the view stays within the domain of the view by using the WITH CHECK OPTION.

SQL> CREATE OR REPLACE VIEW empvu20
  2  AS SELECT *
  3  FROM         emp
  4  WHERE         deptno = 20
  5  WITH CHECK OPTION CONSTRAINT empvu20_ck;

View created.


Any attempt to change the department number for any row in the view will fail because it violates the WITH CHECK OPTION constraint.