Oracle SQL - WITH CHECK OPTION Clause

Introduction

You can change rows with an UPDATE command against the view, and then the rows don't show up in the view anymore.

You add rows with an INSERT command against the view; however, the rows don't show up when you query the view.

Disappearing Updated Rows

Check out what happens by updating four rows, they disappear from the view.

SQL> select * from dept20_v;

SQL> update dept20_v
  2  set    deptno = 30
  3  where  job    ='TRAINER';

4 rows updated.

SQL> select * from dept20_v;

Apparently, the updates in Listing 10-15 are propagated to the underlying  EMPLOYEES table. All trainers from
department 20 don't show up anymore in the DEPT20_V view, because their DEPTNO column value is changed from
20 to 30.

Inserting Invisible Rows

You insert a new row for employee 9999, and you get the message 1 row created. However, the new employee does not show up in the query.

create view dept20_v as
select * from emp where deptno = 20;

insert into dept20_v
values ( 9999,'BOS','D', null, null
         , date '1939-01-01'
         , '10', null, 30);

select * from dept20_v;

Preventing These Two Scenarios

You can create your views with the WITH CHECK OPTION clause to Prevent These Two Scenarios.

You can assign a name to WITH CHECK OPTION constraints, as follows:

create [or replace] view ... with check option constraint <cons-name>;

If you don't provide a constraint name, the Oracle DBMS generates a rather cryptic one for you.

The following code replaces the DEPT20_V view, using WITH CHECK OPTION.

create or replace view dept20_v as
select * from emp where deptno = 20
with check option constraint dept20_v_check;

insert into dept20_v
values ( 9999,'BOS','D', null, null
        , date '1939-01-01'
        , '10', null, 30);
       , '10', null, 30)
    *
ERROR at line 4:
ORA-01402: view WITH CHECK OPTION where-clause violation

• Previous
• Next