Oracle Constraint types: O & V

When query DBA_CONSTRAINTS u will find all the below constraint types

C (check constraint on a table)

P (primary key)

U (unique key)

R (referential integrity)

V (with check option, on a view)

O (with read only, on a view)

(from 11gR2 docs) along with above few more below constraints are added.

H - Hash expression
F - Constraint that involves a REF column
S - Supplemental logging

Read Only Constraint - Type O:

1.Create normal view like below on that we can do both dml as well as select.

create or replace view emp_v1 as select * from EMP;

update emp_v1 set ename='SMITH123' where ename='SMITH';

SELECT * FROM EMP_V1

2. Create view with read only option, means u can perform only select on that if u try to perform dml u will get virtual column not allowed error like below:

create or replace view emp_v2 as select * from EMP WITH READ ONLY;

update emp_v2 set ename='ALEN123' where ename='ALLEN'

ORA-01733: virtual column not allowed here.

Check Option - Type V :

WITH CHECK OPTION prohibits any changes to the table or view that would produce rows that are not included in the subquery.

Case1:try without check option

create or replace view emp_v1 as select * from EMP where sal<3000;

insert into emp_v1(empno,ename,sal) values(1,'ARUN','3500');

Case2:try with check option

create or replace view emp_v2 as select * from EMP where sal<3000 WITH CHECK OPTION;

insert into emp_v2(empno,ename,sal) values(1,'ARUN','3500');

when u perform above insert it raise below exception.

ORA-01402: view WITH CHECK OPTION where-clause violation