How to Use WITH CHECK OPTION on Oracle Views

A view that shows a subset of rows, such as the sales department's employees, can be used to insert and update rows of its base table. Without a safeguard, an UPDATE through the view can move a row out of the subset, or an INSERT can add a row the view will never show. WITH CHECK OPTION forbids both, and WITH READ ONLY forbids changes through the view altogether.

Code for This Guide

The main examples are in the examples/views folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [or replace] view view as subquery
  [with check option [constraint name] | with read only]

With CHECK OPTION, every INSERT and UPDATE through the view must produce rows that satisfy the view's WHERE clause.

Updates That Leave the View

The view shows department 40. Raising a salary through it works; moving an employee to department 50 through it fails.

Example:

create or replace view sales_staff as
select employee_id, first_name, last_name, department_id, salary
from   employees
where  department_id = 40
with check option constraint sales_staff_ck;

update sales_staff set salary = salary + 100 where employee_id = 154;
update sales_staff set department_id = 50 where employee_id = 154;
rollback;

Output:

View SALES_STAFF created.

1 row updated.

Error starting at line : 8
In command -
update sales_staff set department_id = 50 where employee_id = 154
Error at Command Line : 8 Column : 8
Error report -
SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violation

Rollback complete.

The second UPDATE would make the row invisible to the view, so it raises ORA-01402.

Inserts That Do Not Belong

The same rule applies to INSERT: a new row for department 50 cannot be added through the department 40 view.

Example:

-- an INSERT through the view must also produce a row the view can see
insert into sales_staff2 (employee_id, first_name, last_name, department_id, salary, email, hire_date, job_title, base_airport)
values (999, 'Test', 'Person', 50, 5000, 'test.person@nimbus.example', sysdate, 'Analyst', 'DXB');

Output:

Error starting at line : 2
In command -
insert into sales_staff2 (employee_id, first_name, last_name, department_id, salary, email, hire_date, job_title, base_airport)
values (999, 'Test', 'Person', 50, 5000, 'test.person@nimbus.example', sysdate, 'Analyst', 'DXB')
Error at Command Line : 2 Column : 13
Error report -
SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violation

The view for this example was created like the previous one, with the columns needed for an insert, and dropped afterward.

WITH READ ONLY

A view meant for reporting only can forbid all DML.

Example:

create or replace view public_fares as
select route_id, cabin, round(avg(fare)) as typical_fare
from   tickets join flights using (flight_id)
group  by route_id, cabin
with read only;

delete from public_fares;

Output:

View PUBLIC_FARES created.

Error starting at line : 7
In command -
delete from public_fares
Error at Command Line : 7 Column : 13
Error report -
SQL Error: ORA-01732: data manipulation operation not legal on this view

The DELETE fails with ORA-01732. A view with aggregates such as this one could not be changed anyway, but WITH READ ONLY states the intention for any view.

Updatable Join Views

DML on a view changes its base tables. For a view with a join, Oracle reports which columns can be changed in USER_UPDATABLE_COLUMNS, based on which tables are key-preserved: tables whose primary key stays unique in the view's result. The example uses this view, created first:

Example:

create or replace view flight_board as
select f.flight_id, f.flight_no, r.origin, r.destination,
       f.scheduled_departure, f.status
from   flights f
join   routes r on r.route_id = f.route_id;

select flight_no, origin, destination, to_char(scheduled_departure, 'DD-MON') as day, status
from   flight_board
where  origin = 'SYD'
fetch  first 3 rows only;

Output:

View FLIGHT_BOARD created.

FLIGHT_NO    ORIGIN    DESTINATION    DAY       STATUS
____________ _________ ______________ _________ __________
NM149        SYD       AKL            11-JAN    ARRIVED
NM149        SYD       AKL            13-JAN    ARRIVED
NM149        SYD       AKL            01-JAN    ARRIVED

Example:

update flight_board set status = 'CANCELLED' where flight_id = 2800;

select column_name, updatable, insertable, deletable
from   user_updatable_columns
where  table_name = 'FLIGHT_BOARD'
order  by column_name;
rollback;

Output:

1 row updated.

COLUMN_NAME            UPDATABLE    INSERTABLE    DELETABLE
______________________ ____________ _____________ ____________
DESTINATION            NO           NO            NO
FLIGHT_ID              YES          YES           YES
FLIGHT_NO              YES          YES           YES
ORIGIN                 NO           NO            NO
SCHEDULED_DEPARTURE    YES          YES           YES
STATUS                 YES          YES           YES

6 rows selected.

Rollback complete.

Each flight appears once in the view, so FLIGHTS is key-preserved and its columns are updatable; a route appears once per flight, so the ROUTES columns are reported as not updatable. Check this view before relying on DML through a join view, and use an INSTEAD OF trigger for the rest.

Related Guides

• How to Write Semi-Joins with EXISTS in Oracle

Conclusion

WITH CHECK OPTION keeps INSERT and UPDATE through a view within the view's own rows, raising ORA-01402 otherwise, and WITH READ ONLY forbids DML through the view. For join views, USER_UPDATABLE_COLUMNS shows which base table columns can be changed.