Create View ... With Check Option

This post was tested on Oracle 11.2. It shows how to use the WITH CHECK OPTION clause of the CREATE VIEW command. The following two steps create a table and a view on a subset of that table i.e. rows where first_name begins with "A": 

SQL> create table t1
  2  (first_name varchar2(10))
  3  /
 
Table created.
 
SQL> create view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  /
 
View created.
 
SQL>

Oracle allows you to use a view to insert rows which that view would then be unable to select. You can see this below where the view is used to insert a row for "BRIAN". This row can be selected from the table, which shows that it was successfully inserted. However, you cannot select it using the view because first_name does not begin with "A": 

SQL> insert into v1 select 'ANDREW' from dual
  2  /
 
1 row created.
 
SQL> insert into v1 select 'BRIAN' from dual
  2  /
 
1 row created.
 
SQL> select * from t1
  2  /
 
FIRST_NAME
----------
ANDREW
BRIAN
 
SQL> select * from v1
  2  /
 
FIRST_NAME
----------
ANDREW
 
SQL> 

This may not be what you want. For example, you would not expect an administrator in the IT department to be able to insert rows for the Sales department. Let's delete the rows from the table and try something different:

SQL> delete t1
  2  /

2 rows deleted.

SQL>

This time the WITH CHECK OPTION clause is added at the end of the CREATE VIEW statement:

SQL> create or replace view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  with check option
  5  /

View created.

SQL>

This creates a constraint on the view, not the table. It has a system generated name:

SQL> select constraint_name
  2  from dba_constraints
  3  where table_name = 'V1'
  4  /
 
CONSTRAINT_NAME
------------------------------
SYS_C00121817
 
SQL>

With the constraint in place, you can still use the view to insert a first_name which begins with "A":

SQL> insert into v1 select 'ARTHUR' from dual
  2  /
 
1 row created.
 
SQL>

But if you try to use it to insert a first_name beginning with "B", the value is rejected:

SQL> insert into v1 select 'BARRY' from dual
  2  /
insert into v1 select 'BARRY' from dual
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
 
SQL>

... and only the valid value remains in the table:

SQL> select * from t1
  2  /
 
FIRST_NAME
----------
ARTHUR
 
SQL> select * from v1
  2  /
 
FIRST_NAME
----------
ARTHUR
 
SQL>

If you do not like the system generated name, you can choose a name for the constraint yourself like this:

SQL> create or replace view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  with check option constraint c1
  5  /

View created.

SQL> select constraint_name
  2  from dba_constraints
  3  where table_name = 'V1'
  4  /

CONSTRAINT_NAME
------------------------------
C1

SQL>