Sunday, 10 February 2013

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