Views with Constraints:

Let's consider the subsequent example:

Example

CREATE OR REPLACE VIEW empview1 AS SELECT EMPNO,ENAME,DEPTNO,SAL FROM emp WHERE SAL >=3000;

An Insertion onto this view is possible. A record can be inserted that violates the WHERE clause. But although selecting only the WHERE clause is checked and the records which match the condition are only displayed. If a WHERE condition must be checked although inserting records by a view, CHECK OPTION is used. The instance example described this:

CREATE OR REPLACE VIEW empview1 AS SELECT EMPNO,ENAME,DEPTNO,SAL FROM emp WHERE SAL >=3000 WITH CHECK OPTION;

An Oracle raises the subsequent error if any DML operation that violates the WHERE clause is issued:

INSERT INTO empview1 VALUES(14,'Mahesh',20,1000);

INSERT INTO empview1 VALUES(14, 'Mahesh',20,1000)

*

ERROR at line 1:

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