Oracle WITH CHECK OPTION

Summary: in this tutorial, you will learn to use the Oracle WITH CHECK OPTION clause to ensure the view’s consistency.

Oracle WITH CHECK OPTION clause #

In Oracle, simmple views are updatable. It means that you can update, insert, and delete rows of the underlying tables via updatable views.

The problem is that modifying data of the underlying table may expose the rows that do not satisfy the condition in the WHERE clause of the view’s defining query.

For example, the following statement creates a view that includes the rows which meet the condition of the WHERE clause:

CREATE VIEW view_name 
AS
SELECT
  *
FROM
  table_name
WHERE
  filter_condition;Code language: SQL (Structured Query Language) (sql)

When creating a new view, you can use the WITH CHECK OPTION to enforec that any INSERT or UPDATE on the view must result in rows that still satisfy the WHERE condition of the view’s defining query:

CREATE VIEW view_name 
AS
SELECT
  *
FROM
  table_name
WHERE
  filter_condition
WITH CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

Oracle WITH CHECK OPTION example #

We’ll use the brands and cars tables created in the updatable view tutorial for the demonstration.

The following pictures illustrate the data in the brands and cars tables:

SELECT
  *
FROM
  brands;Code language: SQL (Structured Query Language) (sql)

SELECT
  *
FROM
  cars;Code language: SQL (Structured Query Language) (sql)

The following statement creates audi_cars view that returns Audi cars:

CREATE VIEW audi_cars AS
SELECT
  car_id,
  car_name,
  brand_id
FROM
  cars
WHERE
  brand_id = 1;Code language: SQL (Structured Query Language) (sql)

The following query returns the data from the audi_cars view:

SELECT
  *
FROM
  audi_cars;Code language: SQL (Structured Query Language) (sql)

The audi_cars is an updatable view so you can insert a new row into the cars table through it:

INSERT INTO
  audi_cars (car_name, brand_id)
VALUES
  ('BMW Z3 coupe', 2);Code language: SQL (Structured Query Language) (sql)

The statement inserted a row which makes the condition in the WHERE clause ( brand_id = 1) not true.

You can also make the visible rows in the view invisible by updating the data in the underlying base table through the view as shown in the following example:

UPDATE audi_cars
SET
  car_name = 'BMW 1-serie Coupe',
  brand_id = 2
WHERE
  car_id = 3;Code language: SQL (Structured Query Language) (sql)

This statement changed both the brand and name of a car with id 3 from Audi to BMW that makes the row invisible in the view.

SELECT
  *
FROM
  audi_cars;Code language: SQL (Structured Query Language) (sql)

Let’s create another updatable view named ford_cars that has the WITH CHECK OPTION clause:

CREATE VIEW ford_cars AS
SELECT
  car_id,
  car_name,
  brand_id
FROM
  cars
WHERE
  brand_id = 3
WITH CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

The following statement inserts an Audi car into the cars table through the ford_cars view:

INSERT INTO
  ford_cars (car_name, brand_id)
VALUES
  ('Audi RS6 Avant', 1);Code language: SQL (Structured Query Language) (sql)

Unlike the audi_cars view, Oracle issued the following error in this case:

SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violationCode language: SQL (Structured Query Language) (sql)

Since the INSERT statement attempted to insert a row that causes a view WITH CHECK OPTION where-clause violation.

Similarly, the following UPDATE statement also fails to update because of the where-clause violation:

UPDATE ford_cars
SET
  brand_id = 4,
  car_name = 'Honda NSX'
WHERE
  car_id = 6;Code language: SQL (Structured Query Language) (sql)

Summary #

• Use the Oracle WITH CHECK OPTION clause in the CREATE VIEW statement to ensure the view’s consistency.

Quiz #