“with check option” test

There is a table EMP under scott,the detail information go as below

 

 

create a view for “with check option” test

SQL> create or replace view testview as select empno,ename from emp where ename like ‘M%’ with check option;

View created
SQL> select * from testview;

EMPNO       ENAME
———-      ———-
7654           MARTIN
7934            MILLER

update the view which ename is Mile(satisfy the like ‘M%’ condition)

SQL> update testview set ename = ‘Mike’ where empno=7654;

1 row updated.

do another update to the view

SQL> update testview set ename = ‘Robin’ where empno=7654
update testview set ename = ‘Robin’ where empno=7654
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

error occur….because update the ename= Robin violate the “with check option”

 

go on our test… try to insert a row into view “testview”,yes,U can insert,update a view

SQL> insert into testview values (8888,’LANDRY’);
insert into testview values (8888,’LANDRY’)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

//error trigger again while you are trying to insert a row with “LANDRY”,but if you insert a “M***” into the ename column will meet the “with check option”, go as below…
SQL> edit
Wrote file afiedt.buf

1* insert into testview values (8888,’MANDRY’)
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testview;

EMPNO     ENAME
———-    ———-
7654         Mike
7934         MILLER
8888         MANDRY

 

Because we had specify the “with check option” on ename column ,so each row in the testview must meet the condition specified when creating the object “testview”

-EOF-