oracle with check option 的作用

转载

mb649d3a75b51a2 2023-06-30 09:20:15

首先创建表students

SQL> create table students
   2  (
   3  sid number(6) not null unique,
   4  sname varchar2(20) ,
   5  sage varchar2(10)
   6  );
 insert into students values(1,'yangliehui','21');
 insert into students values(2,'zhangsan','22');

然后创建视图view_student

create view view_student as select * from students where sid='2';

再创建视图view_student_check

create view view_student_check as select * from students where sid=2 with check option;

比较view_student 和 view_student_check 的区别：
view_student视图可以执行 insert into view_student_sele values(3,'lisi','30');
view_student_check视图执行insert into view_student_sele values(3,'lisi','30');时显示“视图WITH CHECK OPTION where 子句违规”。
同理：update 、delete 操作也是如此，说明：视图加上with check option 子句后对该视图进行插入、修改、删除操作时，DBMS会自动加上条件（在本例中加的条件是：sid=2）。

insert into (<select clause> WITH CHECK OPTION) values (...)

例如：

SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000 WITH CHECK OPTION)
2 values(999,'testbyhao','testtype');

这样的语法看起来很特殊，其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。

如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。

如果不加WITH CHECK OPTION则在插入时不会检查。

这里注意，subquery其实是不会实际执行的。

例如：

SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000)
2 values(1001,'testbyhao','testtype');

1 row created.


SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000with check option)
2 values(1001,'testbyhao','testtype');
insert into (select object_id,object_name,object_type from xxx where object_id<1000 with check option)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

这里插入的列中没有object_id，也是不允许插入的：

SQL> insert into (select object_name,object_type from xxx where object_id<1000 with check option)
2 values('testbyhao','testtype');
insert into (select object_name,object_type from xxx where object_id<1000 with check option)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

为什么说subquery没有实际执行呢？看统计信息吧：

SQL> set autotrace trace exp stat
SQL> select object_id,object_name,object_type from xxx where object_id<1000;

955 rows selected.
97 consistent gets

SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000)
2 values(999,'testbyhao','testtype');

1 row created.
1 consistent gets