ORACLE WITH CHECK OPTION子句详解

今天一个朋友在问关于创建视图时候WITH CHECK OPTION是什么意思，我当时也没多想，就比较笼统的回答了一下。后来自己想了想，好像自己也记不太清楚了，脑子里只有个大概的印象。

好了话不多说，下面还是通过实验来看一下吧。

C:/Documents and Settings/Admin>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 14 20:22:13 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> show user
USER is ""
SQL> conn scott/tiger
Connected.

SQL> drop table t1;

Table dropped.

SQL> create table t1(id number,name varchar2(20));

Table created.

SQL> insert into t1 values(1,'wh')
  2  ;

1 row created.

SQL> insert into t1 values(2,'wp');

1 row created.

SQL> insert into t1 values(3,'wr');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 wh
         2 wp
         3 wr

SQL> create view v_t1
  2  as
  3  select * from t1
  4  where id=2
  5  with check option;

View created.

 

首先来看一下INSERT

SQL> insert into v_t1 values(1,'haha');
insert into v_t1 values(1,'haha')
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

 

--这里由于视图中定义了where id=2的条件并且加有with check option子句，所以插入id=1的记录就会报错。

SQL> insert into v_t1 values(2,'haha');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 wh
         2 wp
         3 wr
         2 haha

 

--喏，看到了吧，插入id=2的记录就成功了。

 

接下来看看UPDATE

SQL> select * from v_t1;

        ID NAME
---------- --------------------
         2 wp
         2 haha

SQL> update v_t1 set id=1 where name='wp';
update v_t1 set id=1 where name='wp'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

 

--这里如果你把name='wp'的记录中id改为1，那么这条记录就不符合视图定义中id=2的条件了，就会从视图中被删去，这样也是不允许的，所以也就报错了。

SQL> update v_t1
  2  set name='hehe'
  3  where name='haha';

1 row updated.

SQL> select * from v_t1;

        ID NAME
---------- --------------------

         2 wp
         2 hehe

 

--看，这样更改就可以了，因为只是更改了name字段，id字段仍然为2。

 

SQL> update v_t1 set id=11 where name='wh';

0 rows updated.

 

--从这里可以看到，如果某条在表中可是不在视图中的记录，那么你是无法通过视图来修改它的。

 

SQL> update t1 set id=11 where name='wh';

1 row updated.

SQL> select * from t1;

        ID NAME
---------- --------------------
        11 wh
         2 wp
         3 wr
         2 haha

SQL> select * from v_t1;

        ID NAME
---------- --------------------
         2 wp
         2 haha

最后我们看看delete是怎样的

SQL> delete from v_t1 where name='wh';

0 rows deleted.

 

--这里的道理和上面一样，name='wh'这条记录根本就没在视图中显示，你怎么可以通过视图来删除呢？

 

SQL> delete from v_t1 where name='wp';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from v_t1;

        ID NAME
---------- --------------------
         2 haha

SQL> select * from t1;

        ID NAME
---------- --------------------
        11 wh
         3 wr
         2 haha

SQL>

 

总结：我想了下，with check option可以这么解释，通过视图进行的修改，必须也能通过该视图看到修改后的结果。