对with check option创建的视图进行数据插入

OCP考试有这样一道题目：

View the Exhibit and examine the structure of the ORDERS table: 

The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the 
NOT NULL constraint. 
Evaluate the following statement: 
INSERT INTO (SELECT order_id,order_date,customer_id 
FROM ORDERS 
WHERE order_total = 1000 WITH CHECK OPTION) 
VALUES (13, SYSDATE, 101). 
What would be the outcome of the above INSERT statement? 
 
A. It would execute successfully and the new row would be inserted into a 
new temporary table created by the subquery. 
B. It would execute successfully and the ORDER_TOTAL column would have the 
value 1000 inserted automatically in the new row. 
C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it. 
D. It would not execute successfully because all the columns from the ORDERS 
table should have been included in the SELECT list and values should have 
been provided for all the columns. 

Answer: C

其实就是考的往简单视图里插入数据的知识点。

创建一张测试表TT

SQL> create table tt (col1 number primary key, col2 number check(col2>0), col3 number,col4 varchar2(10));

Table created.

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

1 row created.

SQL> insert into tt values (2,2,-1,'b');

1 row created.

SQL> commit;

Commit complete.

根据题目要求进行测试insert数据

SQL> insert into (select col1,col2 from tt where col3>0 with check option) values (3,3);
insert into (select col1,col2 from tt where col3>0 with check option) values (3,3)
     *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

这样的语法看起来很特殊，其实是insert进subquery产生的视图里。

with check option子句的意思是如果不满足subquery里的where条件的话，就不允许插入。

同时where条件中出现的列如果没有默认值，那必须包含在select中，并且在insert的时候要给该列赋值，否则就报上面的ORA-01402错误。

如果where条件中出现的列有默认值，那么insert的时候可以忽略该列，但是默认值必须符合where条件，否则插入也是报错。

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

SQL> select * from tt;

      COL1       COL2       COL3 COL4
---------- ---------- ---------- ----------
         1          1          1 a
         2          2         -1 b
         3          3

SQL> insert into (select col1,col2,col3 from tt where col3>0 with check option)values (4,4,1); 

1 row created.

同样如果把子查询变成视图的话，效果一样

SQL> create view v_tt as select col1,col2 from tt where col3>0 with check option;

View created.

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

SQL> create view v_tt2 as select col1,col2,col3 from tt where col3>0 with check option;

View created.

SQL> insert into v_tt2 values(6,6,6);

1 row created.

SQL> commit;

Commit complete.

新增一列col5，让该列默认值是1

SQL> alter table tt add col5 number default 1;

Table altered.

SQL> insert into (select col1,col2 from tt where col5>0 with check option) values (7,7);

1 row created.

如果是默认值符合where条件，则插入成功。

SQL> insert into (select col1,col2 from tt where col5<0 with check option) values(7,7);
insert into (select col1,col2 from tt where col5<0 with check option) values(7,7)
     *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

如果默认值不符合where条件，插入失败。