with check option使用

SQL> create table t(id number,name char(5));

Table created.

SQL> insert into t values(1,'a');

1 row created.

SQL> create view a1 as select * from t where id=1 with check option;

View created.

 

SQL> select * from a1;

       ID NAME

---------- -----

        1 a

 

SQL> insert into a1 values(2,'a');

insert into a1 values(2,'a')

           *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTIONwhere-clause violation

 

 

SQL> update a1 set id=2 where name='a';

update a1 set id=2 where name='a'

      *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTIONwhere-clause violation

 

在with check option的选项下，可以总结为
1.      update，要保证数据update之后能被视图查询出来，也就是要符合where的条件
2.      insert,保证insert的数据能被视图查询出来
3.      delete,有无 with check option都一样
4.      对于没有where字句的视图，使用with check option是多余的