Creating views that enforce constraints

Tables that underlie views often have constraints that limit the data that can be added to those tables. Views cannot add data to the underlying table that would violate the table's constraints. However, you will be able to define a view to restrict the user's ability to change underlying table data even further, effectively placing a special constraint for data manipulation through the view.

This additional constraint says that insert or update statements issued against the view cannot create rows that the view cannot subsequently select. In other words, if after the change is made, the view will not be able to select the row that has been changed, the view will not allow to make the changes. This view ability constraint is configured when the view is defined by adding the with check option to the create view statement. Let's look at an example to clarify the point:

create or replace view emp_view as
(select empno, ename, job, deptno
from emp
where deptno = 10)
with check option constraint emp_view_constraint;

update emp_view set deptno = 20
where ename = 'KING';

Error comes as:
ORA-01402: view WITH CHECK OPTION where-clause violation

On some systems, you may not get the ORA-01402 error in this context. Instead, Oracle may simply state that zero rows were updated by the statement issued.

You will be also able to use constraints that use > or % operators.
create or replace view emp_view as
(select empno, ename, job, deptno
from emp
where deptno > 1
and deptname like 'A%')
with check option constraint emp_view_constraint;

By the above statement we should mean that all deptno greater than 1 and deptname starting with A are not to be updated.

So the fields used while creating the view cannot be modified. Also you can omit the constraint name from the clause to get the same effect:

create or replace view emp_view as
(select empno, ename, job, deptno
from emp
where deptno = 10)
with check option;

You will be also able to create a view that is read-only by using the following syntax:
create or replace view emp_view as
(select * from emp)
with read only;

Courtesy: OCP Introduction to Oracle 9i: SQL Exam Guide book by Oracle Press
ISBN: 0-07-047420-6