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可以這麼解釋，透過檢視進行的修改，必須也能透過該檢視看到修改後的結果。
http://blog.csdn.net/wanghai__/article/details/4811342